# Brand-owner weekly summary — SQL templates

Approved few-shot patterns the brand-owner weekly summary builds on. Every
column referenced is in `registry.yaml` (`sales`, `stock`, `brand`, `staff`,
`marketing`) so SQL Guard accepts queries that mirror these.

Substitute the literal `'VALLI'` for the FORMAT being summarised. The
chatbot will swap it for KLM/VML/MANDIR/KM at run time.


## Q1: Weekly sales summary — one row per format

Question: How did VALLI do last week — net sales, bills, units, vs LY?

```sql
SELECT FORMAT,
       round(sum(SALE_NET_AMOUNT) / 1e7, 2)                                            AS net_cr,
       sum(SALE_QTY)                                                                   AS units,
       count(distinct saleId)                                                          AS bills,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today() - 7)
               AND SALE_DATE <  toMonday(today())) / 1e7, 2)                            AS cy_week_cr,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today() - 7) - 364
               AND SALE_DATE <  toMonday(today())     - 364) / 1e7, 2)                  AS py_week_cr
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toMonday(today() - 7) - 364
  AND SALE_DATE <  toMonday(today())
  AND lower(trType) = 'sale'
  AND FORMAT = 'VALLI'
GROUP BY FORMAT
```


## Q2: WTD vs MTD vs ABP achievement — single row

Question: For VALLI, what was last week, MTD, and ABP achievement so far this month?

```sql
SELECT FORMAT,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today() - 7)
               AND SALE_DATE <  toMonday(today())) / 1e7, 2)                            AS last_week_cr,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toStartOfMonth(today())
               AND SALE_DATE <  today()) / 1e7, 2)                                      AS mtd_cr,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toStartOfMonth(today()) - 365
               AND SALE_DATE <  today() - 365) / 1e7, 2)                                AS py_mtd_cr
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toStartOfMonth(today()) - 365
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
  AND FORMAT = 'VALLI'
GROUP BY FORMAT
```


## Q3: 13-week trend with PY overlay

Question: Show me VALLI's last 13 weeks of net sales with last-year overlay.

```sql
SELECT toMonday(SALE_DATE)                                AS week_start,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= today() - 13*7) / 1e7, 3) AS cy_cr,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE <  today() - 364
               AND SALE_DATE >= today() - 13*7 - 364) / 1e7, 3) AS py_cr
FROM ERS_SALES_TRANSACTIONS
WHERE (SALE_DATE >= today() - 13*7 OR SALE_DATE >= today() - 13*7 - 364)
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
  AND FORMAT = 'VALLI'
GROUP BY week_start
ORDER BY week_start
```


## Q4: Store grading — A / B / C / D for last week

Question: Grade every VALLI store by last week's growth and ABP achievement.

```sql
WITH weekly AS (
  SELECT shortname,
         ShowroomCity,
         sumIf(SALE_NET_AMOUNT,
               SALE_DATE >= toMonday(today() - 7)
           AND SALE_DATE <  toMonday(today()))                AS cy_rs,
         sumIf(SALE_NET_AMOUNT,
               SALE_DATE >= toMonday(today() - 7) - 364
           AND SALE_DATE <  toMonday(today())     - 364)      AS py_rs
  FROM ERS_SALES_TRANSACTIONS
  WHERE SALE_DATE >= toMonday(today() - 7) - 364
    AND SALE_DATE <  toMonday(today())
    AND lower(trType) = 'sale'
    AND FORMAT = 'VALLI'
  GROUP BY shortname, ShowroomCity
),
abp AS (
  SELECT shortname,
         sumIf(targetdate_achivedSales_in_rs,
               toStartOfMonth(targetdate) = toStartOfMonth(today())
           AND targetdate < today())                          AS achieved_rs,
         sumIf(targetamt,
               toStartOfMonth(targetdate) = toStartOfMonth(today())
           AND targetdate < today())                          AS target_rs
  FROM abp_target_vs_achieved_sales_new
  WHERE FORMAT = 'VALLI'
    AND toStartOfMonth(targetdate) = toStartOfMonth(today())
    AND targetdate < today()
  GROUP BY shortname
)
SELECT w.shortname,
       w.ShowroomCity,
       round(w.cy_rs / 1e5, 2)                                                            AS cy_lakhs,
       round(w.py_rs / 1e5, 2)                                                            AS py_lakhs,
       round((w.cy_rs - w.py_rs) / nullIf(w.py_rs, 0) * 100, 1)                           AS yoy_pct,
       round(a.achieved_rs / nullIf(a.target_rs, 0) * 100, 1)                             AS abp_pct,
       multiIf(
         (w.cy_rs - w.py_rs) / nullIf(w.py_rs, 0) > 0.10
           AND a.achieved_rs / nullIf(a.target_rs, 0) > 0.95, 'A',
         (w.cy_rs - w.py_rs) / nullIf(w.py_rs, 0) BETWEEN 0     AND 0.10
           AND a.achieved_rs / nullIf(a.target_rs, 0) > 0.80, 'B',
         (w.cy_rs - w.py_rs) / nullIf(w.py_rs, 0) BETWEEN -0.10 AND 0
           OR  a.achieved_rs / nullIf(a.target_rs, 0) BETWEEN 0.60 AND 0.80, 'C',
         'D'
       )                                                                                  AS grade
FROM weekly w
LEFT JOIN abp a ON a.shortname = w.shortname
WHERE w.py_rs > 0
ORDER BY yoy_pct ASC
```


## Q5: Section / sub-section performance — last week vs PY

Question: Which sections in VALLI grew or declined most last week vs PY?

```sql
SELECT SALE_SECTION                                                                       AS section,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today() - 7)
               AND SALE_DATE <  toMonday(today())) / 1e5, 2)                              AS cy_lakhs,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today() - 7) - 364
               AND SALE_DATE <  toMonday(today())     - 364) / 1e5, 2)                    AS py_lakhs,
       round((sumIf(SALE_NET_AMOUNT,
                    SALE_DATE >= toMonday(today() - 7)
                AND SALE_DATE <  toMonday(today())) -
              sumIf(SALE_NET_AMOUNT,
                    SALE_DATE >= toMonday(today() - 7) - 364
                AND SALE_DATE <  toMonday(today())     - 364))
             / nullIf(sumIf(SALE_NET_AMOUNT,
                            SALE_DATE >= toMonday(today() - 7) - 364
                        AND SALE_DATE <  toMonday(today())     - 364), 0) * 100, 1)        AS yoy_pct
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toMonday(today() - 7) - 364
  AND SALE_DATE <  toMonday(today())
  AND lower(trType) = 'sale'
  AND FORMAT = 'VALLI'
GROUP BY SALE_SECTION
HAVING py_lakhs > 0 OR cy_lakhs > 0
ORDER BY yoy_pct ASC
```


## Q6: Top vendor groups within the format — last week

Question: Which vendor groups contributed the most net sales for VALLI last week?

```sql
SELECT SALE_VENDORGROUP_NAME                       AS vendor_group,
       round(sum(SALE_NET_AMOUNT) / 1e5, 2)        AS net_lakhs,
       sum(SALE_QTY)                               AS units,
       count(distinct saleId)                      AS bills
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toMonday(today() - 7)
  AND SALE_DATE <  toMonday(today())
  AND lower(trType) = 'sale'
  AND FORMAT = 'VALLI'
  AND SALE_VENDORGROUP_NAME != ''
GROUP BY vendor_group
ORDER BY net_lakhs DESC
LIMIT 20
```


## Q7: Discount erosion last week — by store

Question: Which VALLI stores gave the most discount last week?

```sql
SELECT shortname,
       ShowroomCity,
       round(sum(SALE_GROSS_AMOUNT) / 1e5, 2)                                              AS gross_lakhs,
       round(sum(SALE_NET_AMOUNT)   / 1e5, 2)                                              AS net_lakhs,
       round((sum(SALE_GROSS_AMOUNT) - sum(SALE_NET_AMOUNT))
             / nullIf(sum(SALE_GROSS_AMOUNT), 0) * 100, 1)                                 AS discount_pct,
       round(sum(SALE_MANUAL_DISCOUNT)    / 1e5, 2)                                        AS manual_lakhs,
       round(sum(SALE_EFF_PROMO_DISCOUNT) / 1e5, 2)                                        AS promo_lakhs
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toMonday(today() - 7)
  AND SALE_DATE <  toMonday(today())
  AND lower(trType) = 'sale'
  AND FORMAT = 'VALLI'
GROUP BY shortname, ShowroomCity
ORDER BY discount_pct DESC
LIMIT 20
```


## Q8: Stock health — SOH and old-stock share by section

Question: For VALLI, what's the SOH and old-stock share by section right now?

```sql
SELECT ABP_Section                                                                AS section,
       sum(QTY)                                                                   AS soh_units,
       round(sum(QTY * EFFECTIVE_MRP) / 1e5, 2)                                   AS soh_value_lakhs,
       sumIf(QTY, AGE_RANGE IN ('181-365','366+'))                                AS old_units,
       round(sumIf(QTY, AGE_RANGE IN ('181-365','366+'))
             / nullIf(sum(QTY), 0) * 100, 1)                                      AS old_share_pct
FROM ERS_STOCK_LATEST_DETAILS
WHERE STOCK_DATE = today()
  AND FORMAT = 'VALLI'
GROUP BY ABP_Section
HAVING soh_units > 0
ORDER BY soh_value_lakhs DESC
```


## Q9: MBQ gap — stores with the biggest replenishment shortfall

Question: For VALLI, which stores have the biggest MBQ shortfall today?

```sql
SELECT Store,
       ABP_Section,
       sumIf(Qty, Data_type = 'MBQ')                                                 AS mbq_qty,
       sumIf(Qty, Data_type = 'STOCK')                                               AS stock_qty,
       sumIf(Qty, Data_type = 'MBQ') - sumIf(Qty, Data_type = 'STOCK')               AS gap_units
FROM MBQvsSTOCKvsSALE
WHERE Date = today()
  AND Format = 'VALLI'
GROUP BY Store, ABP_Section
HAVING gap_units > 0
ORDER BY gap_units DESC
LIMIT 50
```


## Q10: Staffing gaps for the format — current week

Question: Which VALLI stores are understaffed against MBQ this week?

```sql
SELECT Store,
       Section,
       sum(required_employees)                                            AS required,
       sum(current_employees)                                             AS current,
       sum(required_employees) - sum(current_employees)                   AS gap
FROM Staff_MBQ_Vs_Current_Staff_Vs_Monthly_Staff
WHERE when_date >= toMonday(today())
  AND when_date <  toMonday(today()) + 7
  AND Format = 'VALLI'
GROUP BY Store, Section
HAVING gap > 0
ORDER BY gap DESC
LIMIT 50
```


## Q11: Customer retention — last week vs PY-week

Question: How is customer retention for VALLI tracking — last week vs same week LY?

```sql
SELECT store,
       sum(total_customers_ly)                                       AS ly_customers,
       sum(retained_customers)                                       AS retained,
       sum(new_customers)                                            AS new_customers,
       sum(churned_customers)                                        AS churned,
       round(sum(retained_customers) / nullIf(sum(total_customers_ly), 0) * 100, 1) AS retention_pct
FROM crm_retention
WHERE period_date >= toMonday(today() - 7)
  AND period_date <  toMonday(today())
GROUP BY store
HAVING ly_customers > 0
ORDER BY retention_pct ASC
LIMIT 30
```