# Sales SQL examples — production pack v1

Approved few-shot patterns for the sales domain. Every column used here is
declared in the schema registry, so SQL Guard will accept queries that
mirror these patterns.


## 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,
       count(distinct saleId)              AS bills,
       sum(SALE_QTY)                       AS units
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= today() - 7
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
GROUP BY FORMAT
ORDER BY net_sales_cr DESC
```


## Q2: Top stores by net sales — month-to-date

Question: Show me the top 20 stores by net sales this month.

```sql
SELECT shortname,
       FORMAT,
       ShowroomCity,
       round(sum(SALE_NET_AMOUNT) / 1e5, 2) AS net_sales_lakhs,
       count(distinct saleId)              AS bills,
       round(sum(SALE_NET_AMOUNT) / count(distinct saleId), 0) AS abv_rs
FROM ERS_SALES_TRANSACTIONS
WHERE toStartOfMonth(SALE_DATE) = toStartOfMonth(today())
  AND SALE_DATE < today()
  AND lower(trType) = 'sale'
GROUP BY shortname, FORMAT, ShowroomCity
ORDER BY net_sales_lakhs DESC
LIMIT 20
```


## Q3: Section-wise sales breakdown — fiscal-year-to-date

Question: Show section-wise net sales for FYTD with LY comparison.

```sql
SELECT SALE_SECTION,
       round(sumIf(SALE_NET_AMOUNT, toYear(SALE_DATE) = toYear(today()) - if(toMonth(today()) >= 4, 0, 1)) / 1e7, 2) AS cy_cr,
       round(sumIf(SALE_NET_AMOUNT, toYear(SALE_DATE) = toYear(today()) - if(toMonth(today()) >= 4, 1, 2)) / 1e7, 2) AS ly_cr
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toDate(concat(toString(toYear(today()) - if(toMonth(today()) >= 4, 1, 2)), '-04-01'))
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
GROUP BY SALE_SECTION
ORDER BY cy_cr DESC
```


## Q4: WTD vs PY-WTD by store — week-on-week growth

Question: How is the week tracking compared to last year?

```sql
SELECT shortname,
       FORMAT,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today())
               AND SALE_DATE <  today()) / 1e5, 2)                                AS wtd_lakhs,
       round(sumIf(SALE_NET_AMOUNT,
                   SALE_DATE >= toMonday(today()) - 364
               AND SALE_DATE <  today() - 364) / 1e5, 2)                          AS py_wtd_lakhs
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toMonday(today()) - 364
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
GROUP BY shortname, FORMAT
HAVING py_wtd_lakhs > 0
ORDER BY wtd_lakhs DESC
LIMIT 50
```


## Q5: Stores below ABP target — current month

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

```sql
SELECT shortname,
       FORMAT,
       ShowroomCity,
       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)
             / nullIf(sum(targetamt), 0) * 100, 1)         AS achievement_pct
FROM abp_target_vs_achieved_sales_new
WHERE toStartOfMonth(targetdate) = toStartOfMonth(today())
  AND targetdate < today()
GROUP BY shortname, FORMAT, ShowroomCity
HAVING target_lakhs > 0 AND achievement_pct < 100
ORDER BY achievement_pct ASC
LIMIT 50
```


## Q6: Daily sales trend — last 30 days

Question: Show daily sales for the last 30 days with LY overlay.

```sql
SELECT SALE_DATE                                                        AS sale_date,
       round(sum(SALE_NET_AMOUNT) / 1e7, 3)                             AS cy_cr,
       count(distinct saleId)                                            AS bills_cy
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= today() - 30
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
GROUP BY SALE_DATE
ORDER BY SALE_DATE
```


## Q7: Discount erosion by store — last quarter

Question: What is the discount percentage by store last quarter?

```sql
SELECT shortname,
       FORMAT,
       round(sum(SALE_GROSS_AMOUNT) / 1e7, 2)                                                  AS gross_cr,
       round(sum(SALE_NET_AMOUNT)   / 1e7, 2)                                                  AS net_cr,
       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 >= today() - 90
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
GROUP BY shortname, FORMAT
ORDER BY discount_pct DESC
LIMIT 50
```


## Q8: Top vendor groups by net sales — fiscal year to date

Question: Which vendor groups contributed the most net sales this fiscal year?

```sql
SELECT SALE_VENDORGROUP_NAME,
       round(sum(SALE_NET_AMOUNT) / 1e7, 2) AS net_cr,
       sum(SALE_QTY)                        AS units,
       count(distinct saleId)               AS bills
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= toDate(concat(
        toString(toYear(today()) - if(toMonth(today()) >= 4, 0, 1)),
        '-04-01'))
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
  AND SALE_VENDORGROUP_NAME != ''
GROUP BY SALE_VENDORGROUP_NAME
ORDER BY net_cr DESC
LIMIT 30
```


## Q9: ASP and ABV by FORMAT — current month

Question: What are the ASP and ABV by format this month?

```sql
SELECT FORMAT,
       sum(SALE_QTY)                                                          AS units,
       count(distinct saleId)                                                  AS bills,
       round(sum(SALE_NET_AMOUNT) / nullIf(sum(SALE_QTY), 0), 0)              AS asp_rs,
       round(sum(SALE_NET_AMOUNT) / nullIf(count(distinct saleId), 0), 0)     AS abv_rs,
       round(sum(SALE_QTY)        / nullIf(count(distinct saleId), 0), 2)     AS abs_units
FROM ERS_SALES_TRANSACTIONS
WHERE toStartOfMonth(SALE_DATE) = toStartOfMonth(today())
  AND SALE_DATE < today()
  AND lower(trType) = 'sale'
GROUP BY FORMAT
ORDER BY abv_rs DESC
```


## Q10: Discount type breakdown — last 30 days

Question: Show me the discount type breakdown for the last 30 days.

```sql
SELECT SALE_DISCOUNT_TYPE,
       round(sum(SALE_NET_AMOUNT) / 1e7, 2)            AS net_cr,
       round(sum(SALE_EFF_PROMO_DISCOUNT) / 1e7, 2)    AS promo_discount_cr,
       count(distinct saleId)                           AS bills
FROM ERS_SALES_TRANSACTIONS
WHERE SALE_DATE >= today() - 30
  AND SALE_DATE <  today()
  AND lower(trType) = 'sale'
  AND SALE_DISCOUNT_TYPE != ''
GROUP BY SALE_DISCOUNT_TYPE
ORDER BY net_cr DESC
```