Page Comparison

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
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
SUM(T1.AMOUNT) AS SWS

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

GROUP BY 1,2,3

SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
SUM(T1.AMOUNT) AS SWS,
SUM(T1.AMOUNT)/COUNT(DISTINCT T1.TICKET_ID) AS CHEQUE

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

GROUP BY 1,2,3

SELECT
T1.TH_FISCAL_YEAR,
T1.TH_FISCAL_WEEK,
CAST(WEEK_START_DT AS DATE) AS WEEK_DT,
COUNT(DISTINCT T1.TICKET_ID) AS TRXNS,
COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT T1.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 T1.IS_PASS_THROUGH = 0 
AND T1.COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2,3

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 = 'WHITE LABEL DELIVERY') OR (SERVICE_MODE_CD = 'DELIVERY' AND REGISTERED_ACCOUNT_ID LIKE 'us-east%'))
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

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 
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 ('KIOSK', 'KIOSK TAKEOUT', 'CATERING', 'CURB SIDE PICK UP')
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
PARTITION_DATE_KEY,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN '$1_START_DATE' AND '$2_END_DATE'
AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT')
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA" 
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) <> 'us-east'

GROUP BY 1

SELECT
PARTITION_DATE_KEY,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND SERVICE_MODE_CD IN ('CATERING', 'CURB SIDE PICK UP')
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA" 
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) <> 'us-east'

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 
PARTITION_DATE_KEY,
COUNT(DISTINCT TRANSACTIONID) AS TICKETS,
SUM(ITEMTOTALPRICE) AS SALES 

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE PARTITION_DATE_KEY >= 20230801
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

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