# Validated SQL examples — <topic>

Each block below is one example BIC will use as a few-shot pattern when it
generates SQL. Keep three rules:

1. **One block per question.** Start with `## Q<N>: <short title>`, then a
   `Question:` line, then a fenced ` ```sql ` block. The chunker splits on
   `## Q` so each block becomes one retrievable example.
2. **Use real table and column names.** Anything not in the schema registry
   will fail SQL Guard and be useless.
3. **Run the SQL once before saving.** A broken example teaches BIC the wrong
   pattern.


## Q1: Net sales by FORMAT — last 7 days

Question: What was net sales by format in the last 7 days?

```sql
SELECT FORMAT,
       round(sum(SALE_NET_AMOUNT) / 1e7, 2) AS net_sales_cr
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= today() - 7
  AND lower(trType) = 'sale'
GROUP BY FORMAT
ORDER BY net_sales_cr DESC
LIMIT 20
```


## Q2: Stores below ABP target — current month

Question: Which stores are below their ABP target this month?

```sql
SELECT shortname,
       round(sum(targetdate_achivedSales_in_rs) / 1e5, 2) AS achieved_lakhs,
       round(sum(targetamt) / 1e5, 2)                      AS target_lakhs,
       round(sum(targetdate_achivedSales_in_rs) / sum(targetamt) * 100, 1) AS achievement_pct
FROM abp_target_vs_achieved_sales_new
WHERE toStartOfMonth(targetdate) = toStartOfMonth(today())
GROUP BY shortname
HAVING target_lakhs > 0 AND achievement_pct < 100
ORDER BY achievement_pct ASC
```


## Q3: Items below MBQ — by store and section

Question: Which items are below MBQ right now?

```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
FROM MBQvsSTOCKvsSALE
WHERE Date = today()
GROUP BY Store, ABP_Section
HAVING gap > 0
ORDER BY gap DESC
LIMIT 50
```