Digital & Loyalty Metrics

SELECT TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS

FROM STG.DERIVED_MASTER_TABLE_NEW

WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SWS

FROM STG.DERIVED_MASTER_TABLE_NEW 
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'

GROUP BY 1,2

SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SWS,
SUM(AMOUNT)/COUNT(DISTINCT TICKET_ID) AS CHEQUE

FROM STG.DERIVED_MASTER_TABLE_NEW 
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'

GROUP BY 1,2

SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT TICKET_ID)/COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS FREQUENCY

FROM STG.DERIVED_MASTER_TABLE_NEW T1
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN '$START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('WHITE LABEL DELIVERY','DELIVERY')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('MOBILE ORDER DRIVE THRU', 'MOBILE ORDER EAT IN', 'MOBILE ORDER TAKE OUT')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('TAKEOUT', 'EATIN','DRIVETHRU')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

SELECT 
A.PARTITION_DATE_KEY,
SUM(A.AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW A 

INNER JOIN TLOG.TLOG_SALE_TICKET_TENDERS B 
ON A.TICKET_ID = B.TICKET_ID

WHERE A.PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'
AND TENDER_NAME IN ("UBER EATS CREDIT","Credit Uber Eats","Uber Eats Credit","CRÉDIT UBER EATS","SKIP CREDIT","Skip Credit","CRÉDIT de DoorDash","CRÉDIT DE DOORDASH","DoorDash Credit","DOORDASH CREDIT","Crédit de DoorDash","Doordash")

GROUP BY 1

SELECT
    LAST_DAY(PERIOD_DT) AS MONTH,
    SUM(AMOUNT) AS KIOSK_SALES,
    COUNT(DISTINCT TICKET_ID) AS KIOSK_TRXNS,
    COUNT(DISTINCT LOYALTY_CUSTOMER_ID) AS KIOSK_GUESTS
  FROM
    STG.DERIVED_MASTER_TABLE_NEW
  WHERE
    PERIOD_DT BETWEEN '$Start_Date'
    AND '$End_Date'
    AND IS_PASS_THROUGH = 0
    AND COUNTRY_NM = 'CANADA'
    AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT', 'KIOSK EATIN')
    AND REST_NO IN (
    <list of restaurants with KIOSKS>
    )
  GROUP BY
    1

-- Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) = 'us-east'
-- Un-Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) != 'us-east'


-- List of Retaurants as of APR 2024
(101789, 101517, 108175, 101493, 102093, 101846, 109331, 102784, 109403, 
108485, 100525, 106376, 105060, 101712, 103498, 109951, 103637, 102534, 
100410, 100062, 104505, 104853, 103482, 103389, 103227, 103217, 102614, 
100788, 108397, 100112, 105789, 100806, 102549, 107228, 101475, 101657, 
100674, 102011, 101129, 104420, 104764, 106305, 101000, 109241, 108358, 
103413, 100027, 100526, 104213, 103704, 105217, 104402, 104271, 102946,
 102376, 102229, 103233, 100940, 104962, 101491, 102118, 102732, 107384,
  101803, 100446, 109041, 109757, 101917, 109878, 102169, 104840, 104013,
   108507, 101886, 100136, 100514, 102017, 102974, 101900, 105085, 
   100467, 100490, 103690, 102467, 102305, 104925, 103167, 101382, 
   108487, 103648, 101281, 102110, 100045, 102398, 103850, 107647, 
   102753, 103124, 102224, 100678, 106882, 101865, 100652, 100503, 
   100693, 102040, 109449, 108102, 105389, 103695, 101666, 101076, 
   100529, 100376, 100856, 102606, 101582, 106547, 107608, 100325, 
   100324, 103340, 102417, 103129, 100544, 101365, 105340, 102074, 
   100096, 108167, 108166, 108088, 107336, 104443, 103384, 103255, 
   103208, 103356, 101450, 103202, 104284, 104856, 101241, 102331, 
   104393, 101820, 104126, 101333, 102556, 102972, 104471, 103568, 
   105792, 103021, 103143, 102833, 103086, 101991, 108399, 109334, 
   109955, 101462, 100582, 100124, 108500, 107576, 101335, 109291,
    103677, 107569, 102635, 105237, 100160, 103077, 102622, 101818, 
    105917, 101600, 102308, 103029, 101992, 102603, 103947, 103137, 
    108172, 101022, 103050, 106865, 103323, 106310, 103169, 102399, 
    102018, 101595, 103407, 101655, 100101, 100506, 100093, 100863,
     103625, 103279, 102891, 101999, 101543, 101476, 102562, 100024, 
     104852, 104370, 109430, 108502, 102852, 103644, 101623, 109405, 
     101837, 100200, 101568, 100885, 100104, 101628, 102991, 102773, 
     102877, 103548, 102821, 102103, 103411, 104887, 102196, 102315, 
     109027, 100998, 102630, 102394, 108402, 101650, 109042, 101873,
      103755, 102925, 103478, 109407, 109397, 109396, 109338, 109286,
       109246, 107582, 103584, 101967, 104970, 106478, 104212, 102212,
        101508, 101368, 100800, 100077, 103950, 103698, 102132, 101851,
         105363, 100670, 102157, 108115, 103001, 103955, 102892, 
         103130, 101769, 101357, 101904, 100118, 101864, 101686, 
         101174, 101924, 109711, 104289, 103886, 102710, 101689, 
         101442, 109404, 109780, 109447, 103351, 100640, 104813, 
         102387, 104275, 109444, 101584, 109448, 108480, 108395, 
         105840, 104651, 103132, 102679, 107653, 106874, 105763, 
         102646, 101849, 100229, 108430, 101985, 102150, 101871, 
         102719, 102060, 120293, 100965, 109436, 108137, 100776, 
         108118, 100387, 102041, 103754, 102409, 102032, 101602,
          108518, 100769, 101788, 109288, 103159, 101954, 109446,
           103294, 103549, 104391, 102129, 102175, 100015, 101528,
            102171, 100967, 103073, 101308, 101552, 100738, 101535, 
            103702, 101870, 103591, 102274, 101159, 102593, 101200, 
            100255, 103659, 104966, 105920, 100489, 100568, 100588, 
            101339, 102001, 103267, 107343, 10363)

SELECT 
PARTITION_DATE_KEY,
SUM(ITEMTOTALPRICE)AS SALES,

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0 
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND DININGTYPE = 'CT'

GROUP BY 1

WITH RESTAURANTS AS (

SELECT 

TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
PARTITION_DATE_KEY,
COUNT(DISTINCT REST_NO) AS RESTAURANTS

FROM STG.DERIVED_MASTER_TABLE_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA' 

GROUP BY 1,2,3
ORDER BY 1)

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(RESTAURANTS) AS RRAMI

FROM RESTAURANTS

GROUP BY 1,2

SELECT 
STOREDATE,
COUNT(DISTINCT TRANSACTIONID) AS TICKETS,
SUM(ITEMTOTALPRICE) AS SALES 

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE STOREDATE BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM IN ('CANADA')
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND SCANANDPAY IS TRUE

GROUP BY 1

CACHE TABLE DAILY_REDEMPTIONS AS

WITH TRANSACTIONS_WITH_OFFER_ID AS (

SELECT 
LEFT(PERIOD_DT, 10) AS PERIOD_DT,
REGISTEREDACCOUNTID,
TRANSACTIONID, 
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE DATE_KEY >= '20230606'
AND COALESCE(REGISTEREDACCOUNTID,'') IS NOT NULL
AND APPLIEDOFFERS IS NOT NULL
AND COUNTRY_NM = 'CANADA')

SELECT 
PERIOD_DT,
COUNT(DISTINCT TRANSACTIONID) AS TRXNS

FROM TRANSACTIONS_WITH_OFFER_ID AS A

INNER JOIN (SELECT DISTINCT OFFERID FROM DYDB.WEEKLYOFFERS
             
            UNION ALL
            
            SELECT DISTINCT OFFERID
            FROM DYDB.OFFERS
            WHERE CONTAINS(description, 'LOYALTY')
            AND DESCRIPTION LIKE 'CA L%'
            AND DESCRIPTION NOT IN ('CA LR Registered Default (same as L1)-LOYALTY', 'CA LU Unregistered (same as 102) ONE-TIME-LOYALTY') ) B
            
ON A.EXPLODED_OFFERS = B.OFFERID 

LEFT JOIN DYDB.OFFERS AS C 
ON A.EXPLODED_OFFERS=C.OFFERID

WHERE EXPLODED_OFFERS IS NOT NULL 
AND C.OFFERID IS NOT NULL

GROUP BY 1

WITH EVENTS AS (
SELECT DATE
       , _TIMHORTONS.INTERACTION.ELEMENT.NAME
       , _TIMHORTONS.LOYALTY.ID AS GUEST_ID
FROM   loyalty.events.adobe_app_events
WHERE  EVENTTYPE IN ('app_open','app_launch')    
AND    _TIMHORTONS.PLATFORM IN ('app') 
AND    LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND    DATE <= CURRENT_DATE )

SELECT     LAST_DAY(EVENTS.DATE) AS DTE
          , COUNT(DISTINCT EVENTS.GUEST_ID) AS ACTIVE_USER
FROM      EVENTS  
GROUP BY  1
ORDER BY  1 DESC

WITH REDEMPTION_SUMMARY AS (
  WITH OFFER_REDEMPTION_TRXNS AS (
    WITH TRANSACTIONS_WITH_OFFER_ID AS (
      SELECT
        TH_FISCAL_YEAR,
        TH_FISCAL_WEEK,
        CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,
        DATE_FORMAT(
          CAST(
            UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
          ),
          'yyyyMMdd'
        ) AS WEEK_START,
        CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
        REGISTEREDACCOUNTID,
        TRANSACTIONID,
        EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
      FROM
        PRODRT.CURATED_TRANS_EVENTS_NEW
      WHERE
        PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
        AND REGISTEREDACCOUNTID LIKE 'us-east%'
        AND COUNTRY_NM = 'CANADA'
    )
    SELECT
      DISTINCT A.TH_FISCAL_YEAR,
      A.TH_FISCAL_WEEK,
      A.WEEK_START_DT,
      A.PERIOD_DT,
      A.REGISTEREDACCOUNTID,
      A.TRANSACTIONID,
      C.NAME,
      C.DESCRIPTION,
      A.EXPLODED_OFFERS,
      1 AS VOLUME
    FROM
      TRANSACTIONS_WITH_OFFER_ID A
      INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID
      LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
  )
  SELECT
    WEEK_START_DT,
    A.DESCRIPTION AS OFFER_DESCRIPTION,
    A.EXPLODED_OFFERS AS OFFERID,
    SUM(VOLUME) AS REDEMPTION_VOLUME
  FROM
    OFFER_REDEMPTION_TRXNS A
  GROUP BY
    1,
    2,
    3
)
SELECT
  a.WEEK_START_DT AS WKDT,
  OFFER_DESCRIPTION,
  OFFERID,
  SUM(REDEMPTION_VOLUME)
FROM
  REDEMPTION_SUMMARY A
  GROUP BY 1,2,3

with TRXN_OFFER_CHALLENGES_REDEMPTIONS as (
      WITH EXPLODED AS (
        SELECT
          TH_FISCAL_YEAR,
          TH_FISCAL_WEEK,
          DATE_FORMAT(
            CAST(
              UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
            ),
            'yyyyMMdd'
          ) AS WEEK_START_MAPPING,
          CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
          REGISTEREDACCOUNTID,
          loyaltyCustomerId,
          TRANSACTIONID,
          EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS,
          REST_TYP_NM,
          OPS_DIV_NM
        FROM
          PRODRT.CURATED_TRANS_EVENTS_NEW -- 1.	Challenge duration: 26/09 to 02/10
        WHERE
          DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}'
          AND '${myapplication.Offer_End_Date}'
          AND COUNTRY_NM = 'CANADA'
          AND REGISTEREDACCOUNTID IS NOT NULL
          AND TRANSACTIONID IS NOT NULL -- AND REST_TYP_NM = "STANDARD"
      )
      SELECT
        DISTINCT A.TH_FISCAL_YEAR,
        A.TH_FISCAL_WEEK,
        A.WEEK_START_MAPPING,
        A.PERIOD_DT,
        A.REGISTEREDACCOUNTID,
        A.loyaltyCustomerId,
        A.TRANSACTIONID,
        A.REST_TYP_NM,
        A.OPS_DIV_NM,
        B.DESCRIPTION,
        1 AS REDEMPTIONS
      FROM
        EXPLODED AS A
        LEFT JOIN DYDB.OFFERS AS B ON A.EXPLODED_OFFERS = B.OFFERID
      WHERE
        EXPLODED_OFFERS = '76290e2e-1783-442f-8ff6-a2b16900a34e'
    ),
    points_issued as (
      Select
        *,
        WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START,
        regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid
      From
        prodrt.curated_points_events as points
      where
        partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'
        AND '${myapplication.Offer_End_Date}'
        and tag = 'PRODUCT_CHALLENGED_COMPLETED'
    ),
    completed as(
      Select
        a.TH_FISCAL_YEAR as TH_FISCAL_YEAR,
        a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,
        a.WEEK_START_MAPPING as WEEK_START_MAPPING,
        a.PERIOD_DT as PERIOD_DT,
        DATE_FORMAT(
          CAST(
            UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
          ),
          'yyyyMMdd'
        ) as transaction_dt,
        a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,
        a.loyaltyCustomerId as loyaltyCustomerId,
        a.TRANSACTIONID as TRANSACTIONID,
        a.REST_TYP_NM as REST_TYP_NM,
        a.OPS_DIV_NM as OPS_DIV_NM,
        a.DESCRIPTION as DESCRIPTION,
        a.REDEMPTIONS as REDEMPTIONS,
        b.transactionID as completion_transactionID,
        b.pointsEarned as pointsEarned,
        b.restaurant as restaurant,
        b.partition_date_key as offer_completion_dt,
        b.WEEK_START as offer_completion_week,
        b.lid as lid
      from
        TRXN_OFFER_CHALLENGES_REDEMPTIONS a
        left join points_issued b on a.loyaltyCustomerId = b.lid
        and b.WEEK_START = a.TH_FISCAL_WEEK
    )
    Select
      TH_FISCAL_YEAR,
      TH_FISCAL_WEEK,
      WEEK_START_MAPPING,
      REGISTEREDACCOUNTID,
      loyaltyCustomerId,
      offer_completion_week,
      count(
        distinct(
          case
            when offer_completion_dt is not NULL
            and transaction_dt <= offer_completion_dt then TRANSACTIONID
          end
        )
      ) as trnx_before_completion,
      count(distinct(TRANSACTIONID)) as total_trnx,
      count(
        distinct (
          case
            when lid is not Null then lid
          end
        )
      ) as completed
    from
      completed
    group by 1, 2, 3, 4, 5, 6

SELECT    YEAR(TIMESTAMP) AS YR
          , MONTH(TIMESTAMP) AS MTH 
          , COUNT(DISTINCT GUESTS) AS GUESTS FROM (
-- WORD CHALLENEGE PLAYERS

SELECT    DISTINCT TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS
FROM      loyalty.events.adobe_app_events 
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge')
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND       _TIMHORTONS.PLATFORM IN ('app')
AND       LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       DATE >= DATE '2023-11-01' 

UNION
-- HOCKEY PLAYERS

SELECT    DISTINCT TIMESTAMP,_TIMHORTONS.LOYALTY.ID  AS GUESTS
FROM      loyalty.events.adobe_app_events
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge'
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks')
AND       _TIMHORTONS.PLATFORM IN ('app')
AND       LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       DATE >= DATE '2023-11-01' 
)
GROUP BY 1,2
ORDER BY 1 DESC, 2

WITH LOYALTY_TENDERS AS
(
    WITH TENDERS AS
    (
      SELECT
      REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME,
      TICKET_ID

      FROM loyalty.tlog.tlog_sale_ticket_tenders

      WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
      AND LEFT(REST_NO,2) = 10
    ),

    LOYALTY AS
    (
      SELECT 
      TICKET_ID AS TICKET_ID

      FROM STG.DERIVED_MASTER_TABLE_NEW

      WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
      AND COUNTRY_NM = 'CANADA'
      AND IS_PASS_THROUGH = 0 
      AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
    )

    SELECT
    A.*

    FROM TENDERS A

    INNER JOIN LOYALTY B
    ON A.TICKET_ID = B.TICKET_ID
    )

SELECT
CASE
          WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH'

          WHEN UPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT'

          WHEN UPPER(TENDER_NAME) IN ('VISA', 'MASTERCARD', 'MASTER CARD', 'AMEX', 'AMERICAN EXPRESS', 'CREDIT CARD', 'CREDIT CARDS', 'DISCOVER', 'M/C', 'DISCOVER CARD', 'DIGITAL AMEX', 'DIGITAL DISCOVER', 'DIGITAL MASTER CARD', 'DIGITAL MASTERCARD', 'DIGITAL AMERICAN EXPRESS', 'DIGITAL VISA', 'DO VISA', 'DO MASTERCARD') THEN 'CREDIT'

          WHEN UPPER(TENDER_NAME) IN ('TIM CARD', 'DIGITAL TIM CARD', 'CARTE TIM', 'MOBILE TIM CARD', 'DIGITAL CARTE TIM', 'TIM CARTE', 'TIMS GIFT CARD', 'DIGITAL TIMS GIFT CARD', 'CARTE-CADEAU TIM', 'DIGITAL CARTE-CADEAU TIM') THEN 'TIMCARD'

          WHEN UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP'

          WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER'

          WHEN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH'

          WHEN UPPER(TENDER_NAME) IN ('SCAN AND PAY VISA', 'SCAN AND PAY MASTERCARD', 'SCAN AND PAY TIMCARD', 'SCAN AND PAY AMEX', 'SCAN AND PAY DISCOVER', 'SCAN AND PAY TIM CARD', 'NUMERISEZ ET PAYEZ – VISA', 'SCANTOPAY', 'NUMERISEZ ET PAYEZ – MASTERCARD', 'NUMERISEZ ET PAYEZ – CARTE TIM', 'NUMÉRISEZ ET PAYEZ – VISA', 'NUMÉRISEZ ET PAYEZ – MASTERCARD', 'NUMÉRISEZ ET PAYEZ – CARTE TIM', 'NUMERISEZ ET PAYEZ – AMEX', 'NUMÉRISEZ ET PAYEZ – AMEX', 'SCAN AND PAY TIMS GIFT CARD') THEN 'SCANANDPAY'

          WHEN UPPER(TENDER_NAME) IN ('HST', 'HST1', 'TVQ', 'TPS', 'GST', 'TAX', 'PST', 'HST TAXABLE SALES', 'SALES TAX' 'HST 1', 'H.S.T.1', 'H.S.T', 'HST 13% TAXABLE SALES', 'GST TAXABLE SALES', 'TAX 1', 'HST 1', 'GST# 75696 6891 RT0001', 'HST # 897258141', 'HST5%', 'HST8%', 'QST', 'SALES TAX', 'MEAL PLAN CARD - PREPAID TAX', 'CARTE PLAN REPAS - TAX PREPAYEE', 'GST # 121071781RT0001', 'TVH', 'TVH1', 'VAF', 'H.S.T.') THEN 'TAX'

          WHEN ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP'

          ELSE 'OTHER'
END AS TENDER,
COUNT(DISTINCT TICKET_ID) AS TRXNS

FROM LOYALTY_TENDERS

GROUP BY 1

With base as(
select
  period_dt,
  state_nm, 
  detail_type,
  service_mode_cd,
  ticket_details_key,
  ticket_details_pos_no,
  a.ticket_details_pos_nm,
  B.discount_key,
  B.discount_cd,
  B.discount_nm,
  C.coupon_key,
  C.coupon_cd,
  C.coupon_offr_nm,
  D.Category,
  SUM(amount) as amt, 
  count(distinct(ticket_id)) as TRXNS
from
  STG.DERIVED_MASTER_TABLE_NEW a
  left join loyalty.tlog.dim_discount b on a.ticket_details_key = b.discount_key
  left join tlog.dim_coupon c on a.ticket_details_key = c.coupon_key
  left join loyalty.analytics.clearview_mapping_discount_types d on a.ticket_details_pos_nm = d.ticket_details_pos_nm
where amount < 0 
  and partition_date_key between <Start_Date> and <End_Date>
  AND COUNTRY_NM = 'CANADA' 
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
)
select
  last_Day(period_dt) as month_dt,
  detail_type,
  ticket_details_pos_nm,
  discount_nm,
  coupon_offr_nm,
  Category,
  sum(amt),
  sum(TRXNS) As trxns
from
  base
group by 1, 2, 3, 4, 5, 6

loyalty.analytics.clearview_mapping_discount_types

-- Total Summary
with clean_loyalty_transactions as(
  with FILTERED as(
    WITH SEPERATED AS (
      -- Filter for Exploded offers for offer ids and explode the disc amt with corresponding PLU
      with zipped as (
        -- explode offers and combine the Discount amount with offer
        SELECT
          *,
          arrays_zip(discountAmounts, appliedOffers) as comb --   EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
        FROM
          PRODRT.CURATED_TRANS_EVENTS_NEW
        WHERE
          partition_date_key >= 20201201
          -- to_date(partition_date_key, "yyyyMMdd") BETWEEN DATE '$Start_Date' AND DATE '$End_Date'
          AND LEFT(REGISTEREDACCOUNTID, 7) = 'us-east'
          AND APPLIEDOFFERS IS NOT NULL
          AND COUNTRY_NM = 'CANADA'
      )
      select
        DISTINCT transactionId,
        loyaltyCustomerId,
        registeredAccountId,
        state_nm,
        ticketId,
        period_dt,
        th_fiscal_week,
        th_fiscal_year,
        week_start_dt,
        explode(Comb) as test
      from
        zipped
    )
    SELECT
      *,
      test ['discountAmounts'] AS DISC_AMT,
      test ['appliedOffers'] AS APPLIED_OFFER
    FROM
      SEPERATED
  )
  select
    FILTERED.*,
    O.NAME,
    O.DESCRIPTION,
    1 as VOLUME
  From
    FILTERED
    inner JOIN DYDB.OFFERS O ON O.OFFERID = FILTERED.APPLIED_OFFER
)
SELECT
  TH_FISCAL_YEAR,
  th_fiscal_week,
  date(week_start_dt) as wk_start_dt,
  state_nm, 
  APPLIED_OFFER,
  DESCRIPTION,
  SUM(DISC_AMT) AS DISCOUNT_DOLLARS,
  SUM(VOLUME) AS REDEMPTION_VOLUME
FROM
  clean_loyalty_transactions
GROUP BY
  1,2,3,4,5,6