Page Comparison

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
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
AND
COUNTRY_NMGROUP = '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 REDEMPTION_SUMMARY AS (
  WITH OFFER_REDEMPTION_TRXNS AS (
    WITH TRANSACTIONS_WITH_OFFER_ID AS (
      SELECT
        TH_FISCAL_YEAR,     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 TH_FISCAL_WEEK,
 COUNTRY_NM = 'CANADA'
    )
 CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,SELECT
         DATE_FORMAT(DISTINCT A.TH_FISCAL_YEAR,
      A.TH_FISCAL_WEEK,
   CAST(   A.WEEK_START_DT,
         UNIX_TIMESTAMP(LEFT(WEEK_START_A.PERIOD_DT,
10), 'yyyy-MM-dd') AS TIMESTAMP    A.REGISTEREDACCOUNTID,
      )A.TRANSACTIONID,
      C.NAME,
   'yyyyMMdd'   C.DESCRIPTION,
     ) AS WEEK_START A.EXPLODED_OFFERS,
      1 AS CAST(PERIOD_DTVOLUME
AS DATE) AS PERIOD_DT, FROM
       REGISTEREDACCOUNTID,TRANSACTIONS_WITH_OFFER_ID A
      INNER JOIN DYDB.WEEKLYOFFERS TRANSACTIONID,B ON A.EXPLODED_OFFERS = B.OFFERID
     EXPLODE(APPLIEDOFFERS) AS EXPLODED_ LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
 FROM )
  SELECT
    PRODRT.CURATEDWEEK_TRANS_EVENTS_NEWSTART_DT,
    A.DESCRIPTION AS WHEREOFFER_DESCRIPTION,
    A.EXPLODED_OFFERS AS OFFERID,
 PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
  SUM(VOLUME) AS REDEMPTION_VOLUME
  FROM
    OFFER_REDEMPTION_TRXNS A
AND REGISTEREDACCOUNTID LIKE 'us-east%'GROUP BY
    1,
   AND COUNTRY_NM2,
= 'CANADA'   3
 )
    SELECT
  a.WEEK_START_DT AS WKDT,
 DISTINCT A.TH_FISCAL_YEAROFFER_DESCRIPTION,
  OFFERID,
   A.TH_FISCAL_WEEK,
SUM(REDEMPTION_VOLUME)
FROM
  REDEMPTION_SUMMARY   A.WEEK_START_DT,
  GROUP    A.PERIOD_DT,BY 1,2,3

with TRXN_OFFER_CHALLENGES_REDEMPTIONS as EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS,
    WITH EXPLODED AS (   REST_TYP_NM,
     SELECT     OPS_DIV_NM
     TH_FISCAL_YEAR,   FROM
          TH_FISCAL_WEEK,
     PRODRT.CURATED_TRANS_EVENTS_NEW -- 1.	Challenge duration: 26/09 to 02/10
    DATE_FORMAT(    WHERE
        CAST(  DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}'
          UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMPAND '${myapplication.Offer_End_Date}'
          AND COUNTRY_NM = 'CANADA'
  ),        AND REGISTEREDACCOUNTID IS NOT NULL
'yyyyMMdd'          AND )TRANSACTIONID AS WEEK_START_MAPPING,
     IS NOT NULL -- AND REST_TYP_NM = "STANDARD"
    CAST(PERIOD_DT AS DATE)
AS PERIOD_DT,     SELECT
     REGISTEREDACCOUNTID,   DISTINCT A.TH_FISCAL_YEAR,
      loyaltyCustomerId,  A.TH_FISCAL_WEEK,
        TRANSACTIONIDA.WEEK_START_MAPPING,
          EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERSA.PERIOD_DT,
          REST_TYP_NMA.REGISTEREDACCOUNTID,
        A.loyaltyCustomerId,
 OPS_DIV_NM       A.TRANSACTIONID,
 FROM       A.REST_TYP_NM,
     PRODRT.CURATED_TRANS_EVENTS_NEW -- 1.	Challenge duration: 26/09 to 02/10
    A.OPS_DIV_NM,
        B.DESCRIPTION,
      WHERE  1 AS REDEMPTIONS
      DATE_KEYFROM
BETWEEN '${myapplication.Offer_Start_Date}'       EXPLODED AS A
 AND '${myapplication.Offer_End_Date}'      LEFT JOIN DYDB.OFFERS AS B ANDON COUNTRYA.EXPLODED_NMOFFERS = 'CANADA'B.OFFERID
      WHERE
   AND REGISTEREDACCOUNTID IS NOT NULL EXPLODED_OFFERS = '76290e2e-1783-442f-8ff6-a2b16900a34e'
    ),
  AND TRANSACTIONID ISpoints_issued NOTas NULL(
-- AND REST_TYP_NM = "STANDARD"  Select
    )    *,
  SELECT      WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS DISTINCT A.TH_FISCAL_YEARWEEK_START,
        A.TH_FISCAL_WEEK,
   regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid
    A.WEEK_START_MAPPING,  From
        Aprodrt.PERIOD_DT,curated_points_events as points
      where
 A.REGISTEREDACCOUNTID,       partition_date_key BETWEEN A.loyaltyCustomerId,'${myapplication.Offer_Start_Date}'
        A.TRANSACTIONID,AND '${myapplication.Offer_End_Date}'
        and tag = A.REST_TYP_NM,'PRODUCT_CHALLENGED_COMPLETED'
    ),
   A.OPS_DIV_NM,
  completed as(
      B.DESCRIPTION,Select
        1 AS REDEMPTIONSa.TH_FISCAL_YEAR as TH_FISCAL_YEAR,
      FROM  a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,
    EXPLODED AS A  a.WEEK_START_MAPPING as WEEK_START_MAPPING,
    LEFT JOIN DYDB.OFFERS AS B ON A.EXPLODED_OFFERS = B.OFFERIDa.PERIOD_DT as PERIOD_DT,
        DATE_FORMAT(
WHERE         EXPLODED_OFFERS = '76290e2e-1783-442f-8ff6-a2b16900a34e'CAST(
     ),     points_issued as UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
  Select        ),
*,         WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START, 'yyyyMMdd'
        ) as regexptransaction_extract(barcodedt,
'(\\d+)|(\\d+)', 0) as lid     a.REGISTEREDACCOUNTID as FromREGISTEREDACCOUNTID,
        prodrt.curated_points_eventsa.loyaltyCustomerId as points loyaltyCustomerId,
        a.TRANSACTIONID as whereTRANSACTIONID,
        partitiona.REST_dateTYP_keyNM BETWEEN '${myapplication.Offer_Start_Date}'as REST_TYP_NM,
        AND '${myapplication.Offer_End_Date}'
 a.OPS_DIV_NM as OPS_DIV_NM,
      and tag = 'PRODUCT_CHALLENGED_COMPLETED'
a.DESCRIPTION as DESCRIPTION,
   ),     completeda.REDEMPTIONS as( REDEMPTIONS,
     Select         a.TH_FISCAL_YEARb.transactionID as THcompletion_FISCAL_YEARtransactionID,
        a.TH_FISCAL_WEEKb.pointsEarned as TH_FISCAL_WEEKpointsEarned,
        a.WEEK_START_MAPPINGb.restaurant as WEEK_START_MAPPINGrestaurant,
        ab.PERIODpartition_date_DTkey as PERIODoffer_completion_DTdt,
        DATE_FORMAT(
 b.WEEK_START as offer_completion_week,
        CAST(b.lid as lid
      from
   UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP TRXN_OFFER_CHALLENGES_REDEMPTIONS a
        ),left join points_issued b on a.loyaltyCustomerId = b.lid
   'yyyyMMdd'     and b.WEEK_START = a.TH_FISCAL_WEEK
) as transaction_dt,  )
    Select
 a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,   TH_FISCAL_YEAR,
     a.loyaltyCustomerId as loyaltyCustomerId TH_FISCAL_WEEK,
      WEEK_START_MAPPING,
 a.TRANSACTIONID as TRANSACTIONID,   REGISTEREDACCOUNTID,
     a.REST_TYP_NM as REST_TYP_NM loyaltyCustomerId,
        a.OPS_DIV_NM as OPS_DIV_NMoffer_completion_week,
      count(
 a.DESCRIPTION as DESCRIPTION,     distinct(
   a.REDEMPTIONS as REDEMPTIONS,     case
   b.transactionID as completion_transactionID,       when  b.pointsEarned as pointsEarned,offer_completion_dt is not NULL
        b.restaurant as restaurant,  and transaction_dt      b.partition_date_key as offer_completion_dt,<= offer_completion_dt then TRANSACTIONID
        b.WEEK_START as offer_completion_week, end
        b.lid)
as lid     )  fromas trnx_before_completion,
      count(distinct(TRANSACTIONID)) as TRXN_OFFER_CHALLENGES_REDEMPTIONS atotal_trnx,
      count(
   left join points_issued b on a.loyaltyCustomerIddistinct =(
b.lid         and b.WEEK_STARTcase
= a.TH_FISCAL_WEEK     )     Select when lid is not Null  TH_FISCAL_YEAR,then lid
      TH_FISCAL_WEEK,    end
  WEEK_START_MAPPING,      )
REGISTEREDACCOUNTID,      ) loyaltyCustomerId,as completed
    from
offer_completion_week,      completed
count(    group by 1, 2, 3, distinct(4, 5, 6

SELECT_TIMHORTONS.PLATFORM IN ('app')
AND       YEAR(TIMESTAMPLEFT(_TIMHORTONS.LOYALTY.ID,7) AS YR
= 'us-east'
AND          , MONTH(TIMESTAMP) AS MTH 
     _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
AND     , COUNT(DISTINCT GUESTS)DATE 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')
ANDDATE '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'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', 'NUMERISEZ ET PAYEZ CARTE-CADEAU TIM') 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 coalesce(cast(isCustomerServiceVisit as string), '') as ISCUSTOMERSERVICEVISIT,
                  EXPLODE(APPLIEDOFFERDETAILS) AS EXPLODED
            
                  FROM PRODRT.CURATED_POINTS_EVENTS A

                  WHERE TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd') BETWEEN DATE '2024-05-01' AND DATE '2024-05-31' --UPDATE THIS
                  AND LEFT(BARCODE, 4) = '0463'
                  AND COALESCE(PARTNERID, '') = '' 
                  AND ISCUSTOMERSERVICEVISIT IS NOT TRUE
                  ) A

            GROUP BY 1
),

ALL_OTHER AS
(
            SELECT 
            LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT,
            SUM(CASE WHEN B.TAG IN ('DAYPART_CHALLENGED_COMPLETED','PRODUCT_CHALLENGED_COMPLETED','FREQUENCY_CHALLENGED_COMPLETED') THEN B.POINTSEARNED ELSE 0 END) AS CHALLENGES,
            SUM(CASE WHEN LEFT(TAG, 4) = 'RUTR' THEN POINTSEARNED ELSE 0 END) AS RUTW,
            SUM(CASE WHEN LEFT(B.TAG, 6) = 'HOCKEY' THEN B.POINTSEARNED ELSE 0 END) AS HOCKEY,
            SUM(CASE WHEN B.ISCUSTOMERSERVICEVISIT = 'true' THEN B.POINTSEARNED ELSE 0 END) AS GUEST_CARE,
            SUM(CASE WHEN UPPER(B.TAG) = 'WORD_CHALLENGE_LEVEL' THEN B.POINTSEARNED ELSE 0 END) AS WORD_CHALLENGE,
            SUM(B.POINTSEARNED) AS TOTAL_POINTS

            FROM PRODRT.CURATED_POINTS_EVENTS B

            WHERE TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd') BETWEEN DATE '2024-05-01' AND DATE '2024-05-31' --UPDATE THIS
            AND LEFT(BARCODE, 4) = '0463'
            AND COALESCE(PARTNERID, '') = '' 

            GROUP BY 1
)

SELECT 
A.MONTH_DT,
BASE,
OFFERS,
CHALLENGES,
RUTW,
HOCKEY,
GUEST_CARE,
WORD_CHALLENGE,
TOTAL_POINTS

FROM OFFER_BASE A 

LEFT JOIN ALL_OTHER B
ON A.MONTH_DT = B.MONTH_DT