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')
        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

SELECT    YEAR(TIMESTAMP) AS PERIOD_DT,YR
          REGISTEREDACCOUNTID, MONTH(TIMESTAMP) AS MTH 
      loyaltyCustomerId,           TRANSACTIONID, COUNT(DISTINCT GUESTS) AS GUESTS       EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS,
   FROM (
-- WORD CHALLENEGE PLAYERS

SELECT    DISTINCT TIMESTAMP,  REST_TYP_NM,
  _TIMHORTONS.LOYALTY.ID AS GUESTS
FROM        OPS_DIV_NMloyalty.events.adobe_app_events 
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH)  FROM
  IN ('/timswordchallenge')
AND        PRODRT.CURATED_TRANS_EVENTS_NEW -- 1.	Challenge duration: 26/09 to 02/10TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND       _TIMHORTONS.PLATFORM IN ('app')
WHEREAND           DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}'
LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId AND '${myapplication.Offer_End_Date}'
    FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
AND      AND COUNTRY_NMDATE >= 'CANADADATE '2023-11-01' 

UNION
-- HOCKEY PLAYERS

SELECT   AND REGISTEREDACCOUNTID IS NOT NULL
 DISTINCT TIMESTAMP,_TIMHORTONS.LOYALTY.ID  AS GUESTS
FROM      loyalty.events.adobe_app_events
WHERE  AND TRANSACTIONID IS NOT NULL -- TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge'
AND REST_TYP_NM = "STANDARD"    TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME)   IN ('submit_picks')
AND      SELECT _TIMHORTONS.PLATFORM IN ('app')
AND      DISTINCT A.TH_FISCAL_YEAR,
  LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       A.TH_FISCAL_WEEK,
        A.WEEK_START_MAPPING,
  _TIMHORTONS.LOYALTY.ID IN (SELECT DISTINCT registeredAccountId FROM loyalty.users.customer_base WHERE LEFT(loyaltyCustomerId,4) IN ('0463','0473'))
AND      A.PERIOD_DT, DATE >= DATE '2023-11-01' 
)
GROUP BY A.REGISTEREDACCOUNTID1,2
ORDER BY 1 DESC, 2

WITH LOYALTY_TENDERS AS
 A.loyaltyCustomerId,(
    WITH TENDERS AS
 A.TRANSACTIONID,   (
     A.REST_TYP_NM,
 SELECT
       A.OPS_DIV_NMREPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME,
      TICKET_ID

 B.DESCRIPTION,     FROM loyalty.tlog.tlog_sale_ticket_tenders

 1 AS REDEMPTIONS   WHERE PARTITION_DATE_KEY BETWEEN 20230101 FROMAND 20230731
       EXPLODED AS AAND LEFT(REST_NO,2) = 10
    ),

  LEFT JOIN DYDB.OFFERSLOYALTY AS
 B ON A.EXPLODED_OFFERS =(
B.OFFERID      SELECT WHERE
      TICKET_ID AS EXPLODEDTICKET_OFFERSID

= '76290e2e-1783-442f-8ff6-a2b16900a34e'     ),
FROM STG.DERIVED_MASTER_TABLE_NEW

  points_issued as (  WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND Select20230731
      AND COUNTRY_NM *,= 'CANADA'
      AND WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START,IS_PASS_THROUGH = 0 
      AND regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lidLEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
    )

    SELECT
From    A.*

   prodrt.curated_points_events as points
 FROM TENDERS A

    whereINNER JOIN LOYALTY B
    ON partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'
    A.TICKET_ID = B.TICKET_ID
    )

SELECT
CASE
   AND '${myapplication.Offer_End_Date}'      WHEN UPPER(TENDER_NAME) IN  and tag = 'PRODUCT_CHALLENGED_COMPLETED'
    ),
    completed as(('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH'

        Select  WHEN UPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT',  a.TH_FISCAL_YEAR as TH_FISCAL_YEAR,
   'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT'

   a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,     WHEN UPPER(TENDER_NAME) IN  a.WEEK_START_MAPPING as WEEK_START_MAPPING,
        a.PERIOD_DT as PERIOD_DT,
        DATE_FORMAT(
          CAST(('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 UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
          ),
          'yyyyMMdd'
   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'

   ) as transaction_dt,     WHEN UPPER(TENDER_NAME) LIKE  a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,'%SKIP%' THEN 'SKIP'

        a.loyaltyCustomerId as loyaltyCustomerId,
        a.TRANSACTIONID as TRANSACTIONID,WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER'

         a.REST_TYP_NM as REST_TYP_NM,
    WHEN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH'

   a.OPS_DIV_NM as OPS_DIV_NM,     WHEN UPPER(TENDER_NAME) IN  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('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  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,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'

     REGISTEREDACCOUNTID,     WHEN  loyaltyCustomerId,
      offer_completion_week,
 ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP'

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

FROM LOYALTY_TENDERS

caseGROUP BY 1

With base as(
select
  period_dt,
  state_nm, 
  detail_type,
when  offerservice_completionmode_dtcd,
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 = 'element_clicked'
AND     DATE(TIMESTAMP) >= DATE '2023-11-01'
AND     _TIMHORTONS.INTERACTION.PATH = '/timswordchallenge'
AND     _TIMHORTONS.INTERACTION.ELEMENT.NAME = 'play'
AND     _TIMHORTONS.LOYALTY.ID IN (SELECT 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
(
            SELECT 
            MONTH_DT,
            SUM(CASE WHEN A.TAG IS NULL AND LEFT(A.EXPLODED.OFFERID,11) = 'EARN_POINTS' THEN A.EXPLODED.TIMSPOINTSEARNED ELSE 0 END) AS BASE,
            SUM(CASE WHEN LEFT(A.EXPLODED.OFFERID,11) <> 'EARN_POINTS' AND A.EXPLODED.OFFERID IS NOT NULL AND A.TAG IS NULL THEN A.EXPLODED.TIMSPOINTSEARNED ELSE 0 END) AS OFFERS
            

            FROM (SELECT 
                  LAST_DAY(TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd')) AS MONTH_DT,
                  TAG,
                  TRANSACTIONID,
                  POINTSEARNED,
                  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