Page Comparison

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 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,
 
      CAST(WEEK_START_DTMAPPING,
   AS DATE) AS WEEK_START_DTREGISTEREDACCOUNTID,
      loyaltyCustomerId,
 DATE_FORMAT(     offer_completion_week,
      count(
      CAST  distinct(
          case
  UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP      when offer_completion_dt is not NULL
),           'yyyyMMdd' and transaction_dt <= offer_completion_dt then TRANSACTIONID
  ) AS WEEK_START,      end
  CAST(PERIOD_DT AS DATE) AS PERIOD_DT,  )
      REGISTEREDACCOUNTID,) as trnx_before_completion,
      TRANSACTIONID,
 count(distinct(TRANSACTIONID)) as total_trnx,
      EXPLODEcount(APPLIEDOFFERS)
AS EXPLODED_OFFERS       FROMdistinct (
       PRODRT.CURATED_TRANS_EVENTS_NEW   case
   WHERE         PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
 when lid is not Null then lid
      AND REGISTEREDACCOUNTID LIKE 'us-east%' end
       AND COUNTRY_NM)
= 'CANADA'     ) as completed
  SELECT  from
    DISTINCT A.TH_FISCAL_YEAR,  completed
    group  A.TH_FISCAL_WEEKby 1, 2, 3, 4, 5,   A.WEEK_START_DT,
  6

SELECT    YEAR(TIMESTAMP) AS YR
   A.PERIOD_DT,       A.REGISTEREDACCOUNTID,
 , MONTH(TIMESTAMP) AS MTH 
   A.TRANSACTIONID,       C.NAME, COUNT(DISTINCT GUESTS) AS GUESTS FROM  C.DESCRIPTION,
      A.EXPLODED_OFFERS,
      1 AS VOLUME
    FROM
(
-- WORD CHALLENEGE PLAYERS

SELECT    DISTINCT TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS
FROM      loyalty.events.adobe_app_events 
WHERE      TRANSACTIONS_WITH_OFFER_ID A
 TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge')
AND     INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID
  TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND    LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
  )
  SELECT_TIMHORTONS.PLATFORM IN ('app')
AND       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 LOYALTY_TENDERS AS
(
    WITH TENDERS AS
     CAST(
      SELECT
       UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-ddREPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TIMESTAMPTENDER_NAME,
      TICKET_ID

    ),  FROM loyalty.tlog.tlog_sale_ticket_tenders

      WHERE  'yyyyMMdd'
  PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
      AND LEFT(REST_NO,2) AS WEEK_START_MAPPING = 10
    ),

    LOYALTY AS
    CAST(PERIOD_DT AS DATE) AS PERIOD_DT,(
      SELECT 
      TICKET_ID AS TICKET_ID

   REGISTEREDACCOUNTID,   FROM STG.DERIVED_MASTER_TABLE_NEW

     loyaltyCustomerId, WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
    TRANSACTIONID,  AND COUNTRY_NM = 'CANADA'
     EXPLODE(APPLIEDOFFERS) ASAND EXPLODED_OFFERS,
 IS_PASS_THROUGH = 0 
      AND RESTLEFT(REGISTERED_TYPACCOUNT_NMID,7) = 'us-east'
    )

  OPS_DIV_NM  SELECT
    A.*

FROM    FROM TENDERS  A

  PRODRT.CURATED_TRANS_EVENTS_NEW -- 1.	Challenge duration: 26/09 to 02/10 INNER JOIN LOYALTY B
    ON A.TICKET_ID = B.TICKET_ID
WHERE    )

SELECT
CASE
   DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}'     WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT',  AND '${myapplication.Offer_End_Date}'
          AND COUNTRY_NM = 'CANADA''EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH'

          WHEN UPPER(TENDER_NAME) IN  AND REGISTEREDACCOUNTID IS NOT NULL
    ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT'

    AND TRANSACTIONID IS NOT NULL -- ANDWHEN REST_TYP_NM = "STANDARD"
      )
      SELECT
        DISTINCT A.TH_FISCAL_YEAR,
        A.TH_FISCAL_WEEK,
 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'

     A.WEEK_START_MAPPING,     WHEN UPPER(TENDER_NAME) IN  A.PERIOD_DT('TIM CARD', 'DIGITAL TIM CARD', 'CARTE TIM', 'MOBILE   A.REGISTEREDACCOUNTIDTIM CARD', 'DIGITAL CARTE TIM', 'TIM CARTE', 'TIMS   A.loyaltyCustomerIdGIFT CARD', 'DIGITAL TIMS GIFT CARD', 'CARTE-CADEAU TIM', 'DIGITAL  A.TRANSACTIONID,
 CARTE-CADEAU TIM') THEN 'TIMCARD'

     A.REST_TYP_NM,     WHEN UPPER(TENDER_NAME) LIKE  A.OPS_DIV_NM,
'%SKIP%' THEN 'SKIP'

      B.DESCRIPTION,    WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 1'UBER'
AS
REDEMPTIONS       FROM   WHEN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH'

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

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

     a.TH_FISCAL_YEAR as TH_FISCAL_YEAR,   WHEN ((UPPER(TENDER_NAME) LIKE '%ROUND%')   a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,
        a.WEEK_START_MAPPING as WEEK_START_MAPPING,OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP'

          ELSE  a.PERIOD_DT as PERIOD_DT,
        DATE_FORMAT(
          CAST'OTHER'
END AS TENDER,
COUNT(DISTINCT TICKET_ID) AS TRXNS

FROM LOYALTY_TENDERS

GROUP BY 1

  With base as(
     select
      UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
  TH_FISCAL_YEAR,
      period_dt,
       )state_nm,
      detail_type,
   'yyyyMMdd'   ops_div_nm,
     ) as transaction_dt ticket_details_key,
      ticket_details_pos_no,
  a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,  a.ticket_details_pos_nm,
      a.loyaltyCustomerId as loyaltyCustomerId,B.discount_key,
      B.discount_cd,
    a.TRANSACTIONID as TRANSACTIONIDB.discount_nm,

       aC.REST_TYP_NM as REST_TYP_NM,coupon_key,
      C.coupon_cd,
    a.OPS_DIV_NM as OPSC.coupon_DIVoffr_NMnm,
      D.Category,
 a.DESCRIPTION as DESCRIPTION,   SUM(amount) as amt,
    a.REDEMPTIONS as REDEMPTIONS,
  count(distinct(ticket_id)) as TRXNS
    from
 b.transactionID as completion_transactionID,   STG.DERIVED_MASTER_TABLE_NEW a
    b.pointsEarned as pointsEarned,left join loyalty.tlog.dim_discount b on a.ticket_details_key =   b.restaurantdiscount_key
 as restaurant,    left join tlog.dim_coupon c on ba.partitionticket_datedetails_key as= offer_completion_dt,c.coupon_key
      left join bloyalty.analytics.WEEK_START as offer_completion_week,
    clearview_mapping_discount_types d on a.ticket_details_pos_nm = d.ticket_details_pos_nm
   b.lid aswhere
lid      amount from< 0
       TRXN_OFFER_CHALLENGES_REDEMPTIONS a
and quantity > 0
      AND leftCOUNTRY_NM join points_issued b on a.loyaltyCustomerId = b.lid= 'CANADA' -- and rest_no = 100387
       -- and b.WEEKdate_STARTkey >= a.TH_FISCAL_WEEK20240101
    GROUP BY
 )     Select1,
      TH_FISCAL_YEAR2,
      TH_FISCAL_WEEK3,
      WEEK_START_MAPPING4,
      REGISTEREDACCOUNTID5,
      loyaltyCustomerId6,
      offer_completion_week7,
      8,
     count( 9,
      10,
distinct(      11,
    case  12,
      13,
   when offer_completion_dt is not14,
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
            

  WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH'       FROM (SELECT 
                 WHEN UPPERLAST_DAY(TENDER_NAME) IN ('DEBIT CARD'TO_DATE(PARTITION_DATE_KEY, 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT'yyyyMMdd')) AS MONTH_DT,
                  TAG,
     WHEN UPPER(TENDER_NAME) IN ('VISA', 'MASTERCARD', 'MASTER CARD', 'AMEX', 'AMERICAN EXPRESS', 'CREDIT CARD', 'CREDIT CARDS'TRANSACTIONID,
 'DISCOVER', 'M/C', 'DISCOVER CARD', 'DIGITAL AMEX', 'DIGITAL DISCOVER', 'DIGITAL MASTER CARD', 'DIGITAL MASTERCARD', 'DIGITAL AMERICAN EXPRESS', 'DIGITAL VISA'POINTSEARNED,
'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'coalesce(cast(isCustomerServiceVisit as string), '') as ISCUSTOMERSERVICEVISIT,
                  EXPLODE(APPLIEDOFFERDETAILS) AS EXPLODED
            
      WHEN UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP'       FROM PRODRT.CURATED_POINTS_EVENTS A

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

TO_DATE(PARTITION_DATE_KEY, 'yyyyMMdd') BETWEEN DATE '2024-05-01' AND DATE '2024-05-31' --UPDATE THIS
          WHEN UPPER(TENDER_NAME) IN ('SCAN AND PAY VISA', 'SCAN AND PAY MASTERCARD'LEFT(BARCODE, 'SCAN4) 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'

'0463'
                  AND COALESCE(PARTNERID, '') = '' 
                  AND ISCUSTOMERSERVICEVISIT IS NOT TRUE
                  ) A

         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'

   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,
       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