/* =============================================================================
   Market Basket dashboard — Snowflake queries (trailing 13 weeks)
   Audience: Marketing / Loyalty
   Source:   PDI Snowflake replication of BIReportingDB / PDI_Warehouse_1561_01
             (vw_MBH_Snowflake, vw_MBLI_Snowflake, vw_MBLoyalty_Snowflake)
   -----------------------------------------------------------------------------
   HOW TO USE
   1. In each query, edit the FROM in the SOURCES CTE block to point at the
      tables in YOUR Snowflake account. Everything downstream uses those aliases.
   2. Run each numbered query and paste the JSON-shaped results into the matching
      block in market_basket_dashboard.html (search for `// === Q1 ... ===`).
   3. Standard AMG filters baked in:
        - Division_Desc = 'Company Ops'
        - AMG_COT_Desc <> 'Closed'
        - Inside_Sales_Flag = 1   (excludes pump-only baskets)
        - Item_Type = 1           (merchandise — not fuel)
      Loyalty card-prefix gating: 767%, 420767%, 639%, 900% (AMG-issued only).
============================================================================= */


/* -----------------------------------------------------------------------------
   Q1 — KPI strip (one row, trailing 13 weeks)
   Output: total_sales, baskets, units, avg_ticket, items_per_basket,
           loyalty_attach_pct, loyalty_avg_ticket, non_loyalty_avg_ticket,
           loyalty_lift_pct
----------------------------------------------------------------------------- */
WITH SOURCES AS (
    SELECT
        h.MARKETBASKET_HEADER_KEY, h.ORGANIZATION_KEY, h.CALENDAR_KEY,
        h.INSIDE_SALES_FLAG,
        li.PRODUCT_KEY, li.ITEM_TYPE, li.QUANTITY_SOLD, li.EXTENDED_RETAIL,
        c.DAY_DATE,
        o.SITE_ID, o.LOCATION_DESC, o.DIVISION_DESC, o.AMG_COT_DESC,
        CASE WHEN lc.MARKETBASKET_HEADER_KEY IS NOT NULL THEN 1 ELSE 0 END AS HAS_LOYALTY
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    LEFT JOIN (
        SELECT DISTINCT MARKETBASKET_HEADER_KEY
        FROM   MARKETBASKET_LOYALTY_CARDS
        WHERE  LOYALTY_CARD_NUMBER LIKE '767%'
            OR LOYALTY_CARD_NUMBER LIKE '420767%'
            OR LOYALTY_CARD_NUMBER LIKE '639%'
            OR LOYALTY_CARD_NUMBER LIKE '900%'
    ) lc ON lc.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
),
basket_roll AS (
    SELECT
        MARKETBASKET_HEADER_KEY,
        MAX(HAS_LOYALTY)         AS HAS_LOYALTY,
        SUM(QUANTITY_SOLD)       AS UNITS,
        SUM(EXTENDED_RETAIL)     AS BASKET_TOTAL,
        COUNT(*)                 AS LINE_COUNT
    FROM   SOURCES
    GROUP  BY MARKETBASKET_HEADER_KEY
)
SELECT
    SUM(BASKET_TOTAL)                                                AS TOTAL_SALES,
    COUNT(*)                                                         AS BASKETS,
    SUM(UNITS)                                                       AS UNITS,
    SUM(BASKET_TOTAL) / NULLIF(COUNT(*),0)                           AS AVG_TICKET,
    SUM(LINE_COUNT)   / NULLIF(COUNT(*),0)                           AS ITEMS_PER_BASKET,
    100.0 * SUM(HAS_LOYALTY) / NULLIF(COUNT(*),0)                    AS LOYALTY_ATTACH_PCT,
    SUM(CASE WHEN HAS_LOYALTY=1 THEN BASKET_TOTAL END)
        / NULLIF(SUM(CASE WHEN HAS_LOYALTY=1 THEN 1 END),0)          AS LOYALTY_AVG_TICKET,
    SUM(CASE WHEN HAS_LOYALTY=0 THEN BASKET_TOTAL END)
        / NULLIF(SUM(CASE WHEN HAS_LOYALTY=0 THEN 1 END),0)          AS NON_LOYALTY_AVG_TICKET
FROM   basket_roll;


/* -----------------------------------------------------------------------------
   Q2 — Daily trend (sales, baskets, loyalty attach %) — one row per day
----------------------------------------------------------------------------- */
WITH basket_roll AS (
    SELECT
        c.DAY_DATE,
        h.MARKETBASKET_HEADER_KEY,
        SUM(li.EXTENDED_RETAIL) AS BASKET_TOTAL,
        MAX(CASE WHEN lc.MARKETBASKET_HEADER_KEY IS NOT NULL THEN 1 ELSE 0 END) AS HAS_LOYALTY
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    LEFT JOIN (
        SELECT DISTINCT MARKETBASKET_HEADER_KEY
        FROM   MARKETBASKET_LOYALTY_CARDS
        WHERE  LOYALTY_CARD_NUMBER LIKE '767%'
            OR LOYALTY_CARD_NUMBER LIKE '420767%'
            OR LOYALTY_CARD_NUMBER LIKE '639%'
            OR LOYALTY_CARD_NUMBER LIKE '900%'
    ) lc ON lc.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
    GROUP  BY c.DAY_DATE, h.MARKETBASKET_HEADER_KEY
)
SELECT
    DAY_DATE,
    SUM(BASKET_TOTAL)                                AS SALES,
    COUNT(*)                                         AS BASKETS,
    SUM(BASKET_TOTAL) / NULLIF(COUNT(*),0)           AS AVG_TICKET,
    100.0 * SUM(HAS_LOYALTY) / NULLIF(COUNT(*),0)    AS LOYALTY_ATTACH_PCT
FROM   basket_roll
GROUP  BY DAY_DATE
ORDER  BY DAY_DATE;


/* -----------------------------------------------------------------------------
   Q3 — Department breakdown (top departments by sales)
   Uses FULLPRODUCT for Department_Desc.
----------------------------------------------------------------------------- */
WITH SOURCES AS ( /* Q1 SOURCES + product join */
    SELECT
        h.MARKETBASKET_HEADER_KEY,
        li.PRODUCT_KEY, li.ITEM_TYPE,
        li.QUANTITY_SOLD, li.EXTENDED_RETAIL,
        c.DAY_DATE,
        o.DIVISION_DESC, o.AMG_COT_DESC,
        h.INSIDE_SALES_FLAG,
        p.DEPARTMENT_DESC, p.CATEGORY_DESC
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    JOIN   FULLPRODUCT               p  ON p.PRODUCT_KEY = li.PRODUCT_KEY AND p.ITEM_TYPE = li.ITEM_TYPE
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
)
SELECT
    DEPARTMENT_DESC,
    SUM(EXTENDED_RETAIL)                              AS SALES,
    SUM(QUANTITY_SOLD)                                AS UNITS,
    COUNT(DISTINCT MARKETBASKET_HEADER_KEY)           AS BASKETS,
    SUM(EXTENDED_RETAIL)
      / NULLIF(COUNT(DISTINCT MARKETBASKET_HEADER_KEY),0) AS SALES_PER_BASKET
FROM   SOURCES
GROUP  BY DEPARTMENT_DESC
ORDER  BY SALES DESC;


/* -----------------------------------------------------------------------------
   Q4 — Top items (top 25 by sales)
----------------------------------------------------------------------------- */
WITH item_roll AS (
    SELECT
        p.ITEM_ID,
        p.ITEM_DESC,
        p.DEPARTMENT_DESC,
        p.CATEGORY_DESC,
        SUM(li.QUANTITY_SOLD)                            AS UNITS,
        SUM(li.EXTENDED_RETAIL)                          AS SALES,
        COUNT(DISTINCT li.MARKETBASKET_HEADER_KEY)       AS BASKETS_CONTAINING
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    JOIN   FULLPRODUCT               p  ON p.PRODUCT_KEY = li.PRODUCT_KEY AND p.ITEM_TYPE = li.ITEM_TYPE
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
    GROUP  BY p.ITEM_ID, p.ITEM_DESC, p.DEPARTMENT_DESC, p.CATEGORY_DESC
)
SELECT *
FROM   item_roll
ORDER  BY SALES DESC
LIMIT  25;


/* -----------------------------------------------------------------------------
   Q5 — Item co-occurrence (top 50 pairs by lift)
   support  = baskets_containing_both / total_baskets
   conf     = baskets_containing_both / baskets_containing_A
   lift     = conf / (baskets_containing_B / total_baskets)
   Restricted to top-N "candidate" items so the self-join doesn't explode.
----------------------------------------------------------------------------- */
WITH dated_lines AS (
    SELECT
        h.MARKETBASKET_HEADER_KEY,
        p.ITEM_ID,
        p.ITEM_DESC
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    JOIN   FULLPRODUCT               p  ON p.PRODUCT_KEY = li.PRODUCT_KEY AND p.ITEM_TYPE = li.ITEM_TYPE
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
),
candidate_items AS (   -- restrict to top 200 SKUs by basket frequency
    SELECT ITEM_ID, ITEM_DESC, COUNT(DISTINCT MARKETBASKET_HEADER_KEY) AS BASKETS
    FROM   dated_lines
    GROUP  BY ITEM_ID, ITEM_DESC
    QUALIFY ROW_NUMBER() OVER (ORDER BY BASKETS DESC) <= 200
),
filtered AS (
    SELECT DISTINCT dl.MARKETBASKET_HEADER_KEY, dl.ITEM_ID, dl.ITEM_DESC
    FROM   dated_lines dl
    JOIN   candidate_items ci ON ci.ITEM_ID = dl.ITEM_ID
),
totals AS (
    SELECT COUNT(DISTINCT MARKETBASKET_HEADER_KEY) AS TOTAL_BASKETS FROM filtered
),
item_baskets AS (
    SELECT ITEM_ID, ITEM_DESC, COUNT(*) AS BASKETS_CONTAINING
    FROM   filtered
    GROUP  BY ITEM_ID, ITEM_DESC
),
pairs AS (
    SELECT
        a.ITEM_ID    AS ITEM_A_ID,
        a.ITEM_DESC  AS ITEM_A,
        b.ITEM_ID    AS ITEM_B_ID,
        b.ITEM_DESC  AS ITEM_B,
        COUNT(*)     AS BASKETS_BOTH
    FROM   filtered a
    JOIN   filtered b
           ON  a.MARKETBASKET_HEADER_KEY = b.MARKETBASKET_HEADER_KEY
           AND a.ITEM_ID < b.ITEM_ID            -- unordered pairs, no self
    GROUP  BY a.ITEM_ID, a.ITEM_DESC, b.ITEM_ID, b.ITEM_DESC
    HAVING COUNT(*) >= 50                       -- prune long tail
)
SELECT
    p.ITEM_A,
    p.ITEM_B,
    p.BASKETS_BOTH,
    100.0 * p.BASKETS_BOTH / t.TOTAL_BASKETS                                            AS SUPPORT_PCT,
    100.0 * p.BASKETS_BOTH / ia.BASKETS_CONTAINING                                      AS CONFIDENCE_A_TO_B_PCT,
    (p.BASKETS_BOTH * 1.0 / ia.BASKETS_CONTAINING)
      / NULLIF(ib.BASKETS_CONTAINING * 1.0 / t.TOTAL_BASKETS, 0)                        AS LIFT
FROM   pairs p
CROSS  JOIN totals t
JOIN   item_baskets ia ON ia.ITEM_ID = p.ITEM_A_ID
JOIN   item_baskets ib ON ib.ITEM_ID = p.ITEM_B_ID
ORDER  BY LIFT DESC
LIMIT  50;


/* -----------------------------------------------------------------------------
   Q6 — Store performance (one row per site, trailing 13 weeks)
----------------------------------------------------------------------------- */
WITH basket_roll AS (
    SELECT
        o.SITE_ID,
        o.LOCATION_DESC,
        h.MARKETBASKET_HEADER_KEY,
        SUM(li.EXTENDED_RETAIL) AS BASKET_TOTAL,
        MAX(CASE WHEN lc.MARKETBASKET_HEADER_KEY IS NOT NULL THEN 1 ELSE 0 END) AS HAS_LOYALTY
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    LEFT JOIN (
        SELECT DISTINCT MARKETBASKET_HEADER_KEY
        FROM   MARKETBASKET_LOYALTY_CARDS
        WHERE  LOYALTY_CARD_NUMBER LIKE '767%'
            OR LOYALTY_CARD_NUMBER LIKE '420767%'
            OR LOYALTY_CARD_NUMBER LIKE '639%'
            OR LOYALTY_CARD_NUMBER LIKE '900%'
    ) lc ON lc.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
    GROUP  BY o.SITE_ID, o.LOCATION_DESC, h.MARKETBASKET_HEADER_KEY
)
SELECT
    SITE_ID,
    LOCATION_DESC,
    SUM(BASKET_TOTAL)                                AS SALES,
    COUNT(*)                                         AS BASKETS,
    SUM(BASKET_TOTAL) / NULLIF(COUNT(*),0)           AS AVG_TICKET,
    100.0 * SUM(HAS_LOYALTY) / NULLIF(COUNT(*),0)    AS LOYALTY_ATTACH_PCT
FROM   basket_roll
GROUP  BY SITE_ID, LOCATION_DESC
ORDER  BY SALES DESC;


/* -----------------------------------------------------------------------------
   Q7 — Loyalty cohort comparison (loyal vs non-loyal baskets)
----------------------------------------------------------------------------- */
WITH basket_roll AS (
    SELECT
        h.MARKETBASKET_HEADER_KEY,
        SUM(li.EXTENDED_RETAIL) AS BASKET_TOTAL,
        SUM(li.QUANTITY_SOLD)   AS UNITS,
        COUNT(*)                AS LINE_COUNT,
        MAX(CASE WHEN lc.MARKETBASKET_HEADER_KEY IS NOT NULL THEN 1 ELSE 0 END) AS HAS_LOYALTY
    FROM   MARKETBASKET_HEADER       h
    JOIN   MARKETBASKET_LINE_ITEMS   li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    JOIN   CALENDAR                  c  ON c.CALENDAR_KEY  = h.CALENDAR_KEY
    JOIN   DIM_VW_ORGANIZATION       o  ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY
    LEFT JOIN (
        SELECT DISTINCT MARKETBASKET_HEADER_KEY
        FROM   MARKETBASKET_LOYALTY_CARDS
        WHERE  LOYALTY_CARD_NUMBER LIKE '767%'
            OR LOYALTY_CARD_NUMBER LIKE '420767%'
            OR LOYALTY_CARD_NUMBER LIKE '639%'
            OR LOYALTY_CARD_NUMBER LIKE '900%'
    ) lc ON lc.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY
    WHERE  c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE)
      AND  c.DAY_DATE <  CURRENT_DATE
      AND  h.INSIDE_SALES_FLAG = 1
      AND  li.ITEM_TYPE = 1
      AND  o.DIVISION_DESC = 'Company Ops'
      AND  o.AMG_COT_DESC <> 'Closed'
    GROUP  BY h.MARKETBASKET_HEADER_KEY
)
SELECT
    CASE WHEN HAS_LOYALTY=1 THEN 'Loyalty' ELSE 'Non-loyalty' END AS COHORT,
    COUNT(*)                                          AS BASKETS,
    SUM(BASKET_TOTAL)                                 AS SALES,
    SUM(BASKET_TOTAL) / NULLIF(COUNT(*),0)            AS AVG_TICKET,
    SUM(UNITS)        / NULLIF(COUNT(*),0)            AS UNITS_PER_BASKET,
    SUM(LINE_COUNT)   / NULLIF(COUNT(*),0)            AS LINES_PER_BASKET
FROM   basket_roll
GROUP  BY CASE WHEN HAS_LOYALTY=1 THEN 'Loyalty' ELSE 'Non-loyalty' END
ORDER  BY COHORT;


/* =============================================================================
   NOTES / CAVEATS
   - Performance: Q5 is the expensive one. If your basket table is large,
     limit candidate_items to top 100 (instead of 200) or pre-filter by
     STORE/REGION before pairing.
   - 13 weeks = 91 days; we use DATEADD(day, -91, CURRENT_DATE) and exclude
     today (incomplete day).
   - Loyalty attach uses AMG-issued card prefixes (767/420767/639/900). To
     include third-party loyalty programs, drop the LIKE filters in the
     loyalty subquery.
   - Department / Category column names: the AMG skill confirms DEPARTMENT_DESC;
     CATEGORY_DESC is conventional but verify your replicated FULLPRODUCT view
     uses that name (some Snowflake replications flatten naming).
   - Replace <DB>.<SCHEMA> globally before running. If your replication uses a
     view-name suffix like _SF or _SNOWFLAKE, swap MARKETBASKET_LINE_ITEMS for
     MBLI_SF (etc.) — the columns line up.
============================================================================= */
