Page Comparison

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

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    _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
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
           , COUNT(DISTINCT EVENTS.GUEST_ID) AS ACTIVE_USER
FROM when lid is not Null then lid
    EVENTS   GROUP BY  1end
ORDER BY  1 DESC

SELECT    YEAR(TIMESTAMP) AS YR
          TH_FISCAL_WEEK,         CAST(WEEK_START_DT AS DATEMONTH(TIMESTAMP) AS WEEK_START_DT,MTH 
       DATE_FORMAT(   , COUNT(DISTINCT GUESTS) AS GUESTS   FROM CAST(
-- WORD CHALLENEGE PLAYERS

SELECT      DISTINCT UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd'), _TIMHORTONS.LOYALTY.ID AS TIMESTAMPGUESTS
FROM      loyalty.events.adobe_app_events 
WHERE  ),
    TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge')
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'yyyyMMddplay'
AND       _TIMHORTONS.PLATFORM IN ('app')
AND   AS WEEK_START,   LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND   CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
        REGISTEREDACCOUNTID,
  _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
AND      TRANSACTIONID, DATE >= DATE '2023-11-01' 

UNION
 EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
-- HOCKEY PLAYERS

SELECT    DISTINCT  FROM
  TIMESTAMP,_TIMHORTONS.LOYALTY.ID  AS GUESTS
FROM      PRODRTloyalty.events.CURATEDadobe_TRANS_EVENTS_NEW
      app_events
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge'
AND PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
   TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks')
AND      AND REGISTEREDACCOUNTID LIKE 'us-east%'
_TIMHORTONS.PLATFORM IN ('app')
AND        AND COUNTRY_NMLEFT(_TIMHORTONS.LOYALTY.ID,7) = 'CANADAus-east'
AND    )   _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId     DISTINCT A.TH_FISCAL_YEAR,
      A.TH_FISCAL_WEEK,
FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
AND      A.WEEK_START_DT,
      A.PERIOD_DT,
      A.REGISTEREDACCOUNTID,
      A.TRANSACTIONID,
      C.NAME, DATE >= DATE '2023-11-01' 
)
GROUP BY 1,2
ORDER BY 1 DESC, 2

WITH LOYALTY_TENDERS AS
(
    WITH TENDERS AS
    (
      C.DESCRIPTION,SELECT
      A.EXPLODED_OFFERS,
   REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME,
  1 AS VOLUME  TICKET_ID

 FROM       TRANSACTIONS_WITH_OFFER_ID AFROM loyalty.tlog.tlog_sale_ticket_tenders

      INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERIDWHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
        LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERIDAND LEFT(REST_NO,2) = 10
    ),

 SELECT   LOYALTY AS
WEEK_START_DT,    (
A.DESCRIPTION AS OFFER_DESCRIPTION,    SELECT A.EXPLODED_OFFERS
AS OFFERID,     SUM(VOLUME)TICKET_ID AS REDEMPTIONTICKET_VOLUMEID

  FROM    FROM OFFER_REDEMPTION_TRXNS A
  GROUP BY
    1,STG.DERIVED_MASTER_TABLE_NEW

      WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
      AND 2,COUNTRY_NM = 'CANADA'
  3 ) SELECT  AND a.WEEKIS_STARTPASS_DTTHROUGH AS= WKDT,0 
 OFFER_DESCRIPTION,   OFFERID,  AND SUMLEFT(REDEMPTION_VOLUMEREGISTERED_ACCOUNT_ID,7) FROM= 'us-east'
 REDEMPTION_SUMMARY A  )
GROUP
BY 1,2,3

  With base as(
    EXPLODED_OFFERSselect
= '76290e2e-1783-442f-8ff6-a2b16900a34e'     ),TH_FISCAL_YEAR,
      pointsperiod_issueddt,
 as (    state_nm,
  Select    detail_type,
    *,  ops_div_nm,
      WEEKOFYEAR(to_date(partition_date_ticket_details_key, "yyyyMMdd")) AS WEEK_START
      ticket_details_pos_no,
      a.ticket_details_pos_nm,
 regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid     B.discount_key,
      FromB.discount_cd,
        prodrtB.curated_points_events as pointsdiscount_nm,
      C.coupon_key,
  where    C.coupon_cd,
    partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'C.coupon_offr_nm,
      D.Category,
  AND '${myapplication.Offer_End_Date}'   SUM(amount) as amt,
   and tag = 'PRODUCT_CHALLENGED_COMPLETED' count(distinct(ticket_id)) as TRXNS
    from
),     completed as(
 STG.DERIVED_MASTER_TABLE_NEW a
     Select left join loyalty.tlog.dim_discount b on    a.THticket_FISCALdetails_YEARkey as= TH_FISCAL_YEAR,b.discount_key
      left join tlog.dim_coupon c on a.THticket_FISCALdetails_WEEKkey as= TH_FISCAL_WEEK,c.coupon_key
      left join a.WEEK_START_MAPPING as WEEK_START_MAPPING,
        a.PERIOD_DT as PERIOD_DT,loyalty.analytics.clearview_mapping_discount_types d on a.ticket_details_pos_nm = d.ticket_details_pos_nm
    where
   DATE_FORMAT(   amount < 0
     CAST( and quantity > 0
      AND  UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
 COUNTRY_NM = 'CANADA' -- and rest_no = 100387
      -- and ),
 date_key >= 20240101
    GROUP BY
  'yyyyMMdd'    1,
    ) as transaction_dt2,
      3,
 a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,   4,
     a.loyaltyCustomerId as loyaltyCustomerId 5,
      6,
 a.TRANSACTIONID as TRANSACTIONID,    7,
    a.REST_TYP_NM as REST_TYP_NM8,
      9,
 a.OPS_DIV_NM as OPS_DIV_NM,    10,
    a.DESCRIPTION as DESCRIPTION11,
      12,
 a.REDEMPTIONS as REDEMPTIONS,   13,
     b.transactionID as completion_transactionID 14,
        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

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

SELECT LEVEL, COUNT(DISTINCT ID) AS USERS
FROM (
SELECT    _TIMHORTONS.LOYALTY.ID, MAX(DATE(TIMESTAMP)) AS DTE
        , COALESCE(MAX(CAST(SUBSTRING(_TIMHORTONS.INTERACTION.ELEMENT.VALUE,18,CHARINDEX('-',REPLACE(_TIMHORTONS.INTERACTION.ELEMENT.VALUE,'"','-'),18)-18) AS INT)),0) LEVEL
FROM    loyalty.events.adobe_app_events
WHERE   EVENTTYPE 
WHERE= 'element_clicked'
AND     TRIM(DATE(TIMESTAMP) >= DATE '2023-11-01'
AND     _TIMHORTONS.INTERACTION.PATH) IN= ('/timswordchallenge')
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND       _TIMHORTONS.LOYALTY.PLATFORMID 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
FROMSELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
GROUP BY 1
)
GROUP BY 1
ORDER BY 1

WITH OFFER_BASE AS
(
          loyalty.events.adobe_app_events WHERE SELECT 
  TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge' AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks')
ANDMONTH_DT,
        _TIMHORTONS.PLATFORM IN ('app') AND SUM(CASE WHEN A.TAG IS NULL AND LEFT(_TIMHORTONSA.LOYALTYEXPLODED.IDOFFERID,711) = 'us-eastEARN_POINTS' AND       DATE >= DATE '2023-11-01' 
)
GROUP BY 1,2
ORDER BY 1 DESC, 2