SQL Thinking Traces

"Top 2 SKUs by revenue in each region"

SOURCE  (grain = one shipment line)
  region  sku  revenue
  North A 100 · North A 50 · North B 120 · North C 30 · South A 80 · South B 200

── GROUP BY region, sku ──▸  grain COLLAPSES to one row per (region, sku)
  North A 150 · North B 120 · North C 30 · South B 200 · South A 80

── WINDOW ROW_NUMBER() OVER (PARTITION BY region ORDER BY rev DESC) ──▸  column ADDED
  North A 150 rn=1 · North B 120 rn=2 · North C 30 rn=3 · South B 200 rn=1 · South A 80 rn=2

── WHERE rn <= 2 ──▸  drops rows   ★ OUTPUT
  North A 150 · North B 120 · South B 200 · South A 80

WITH per_sku AS (
  SELECT w.region, s.sku_id, SUM(s.revenue) AS rev
  FROM shipments s
  JOIN warehouses w ON w.warehouse_id = s.warehouse_id   -- region lives on warehouses
  WHERE s.status = 'delivered'
  GROUP BY w.region, s.sku_id
), ranked AS (
  SELECT region, sku_id, rev,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY rev DESC) AS rn
  FROM per_sku
)
SELECT region, sku_id, rev FROM ranked WHERE rn <= 2;

"Cumulative delivered revenue by day for warehouse 10"

SOURCE (one shipment line, wh=10, delivered)   ── GROUP BY day ──▸  one row per day
  06-01 100 · 06-01 50 · 06-02 200 · 06-03 80      06-01 150 · 06-02 200 · 06-03 80

── WINDOW SUM(daily) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) ──▸  each row sees PRIOR rows
  06-01 150 run=150 · 06-02 200 run=350 (150+200) · 06-03 80 run=430 (350+80)

Solution 1 — nested window aggregate (one statement; SUM(SUM()))

SELECT ship_date,
       SUM(revenue) AS daily_rev,
       SUM(SUM(revenue)) OVER (ORDER BY ship_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rev
FROM shipments
WHERE warehouse_id = 10 AND status = 'delivered'
GROUP BY ship_date ORDER BY ship_date;

Solution 2 — CTE, no nested SUM (collapse first, then a plain window — identical result, what most people write)

WITH daily AS (                          -- STEP 1: collapse to one row per day
  SELECT ship_date, SUM(revenue) AS daily_rev
  FROM shipments
  WHERE warehouse_id = 10 AND status = 'delivered'
  GROUP BY ship_date
)
SELECT ship_date, daily_rev,             -- STEP 2: run a total across the days
       SUM(daily_rev) OVER (ORDER BY ship_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rev
FROM daily ORDER BY ship_date;

"MoM revenue growth % per warehouse"

SOURCE (one shipment line)        ── GROUP BY wh, month ──▸  one row per wh-month
  1 Jan 100 · 1 Jan 50               1 Jan 150 · 1 Feb 200 · 1 Mar 150
  1 Feb 200 · 1 Mar 150

── WINDOW LAG(rev) OVER (PARTITION BY wh ORDER BY month) ──▸  prior-row col ADDED
  1 Jan 150 prev=NULL · 1 Feb 200 prev=150 · 1 Mar 150 prev=200

── SELECT 100.0*(rev-prev)/NULLIF(prev,0) ──▸  ★ OUTPUT
  1 Jan 150 (—) · 1 Feb 200 (+33.33%) · 1 Mar 150 (-25.00%)

WITH monthly AS (
  SELECT warehouse_id, DATE_TRUNC('month', ship_date) AS mth, SUM(revenue) AS rev
  FROM shipments GROUP BY warehouse_id, DATE_TRUNC('month', ship_date)
)
SELECT warehouse_id, mth, rev,
       ROUND(100.0 * (rev - LAG(rev) OVER (PARTITION BY warehouse_id ORDER BY mth))
             / NULLIF(LAG(rev) OVER (PARTITION BY warehouse_id ORDER BY mth), 0), 2) AS mom_pct
FROM monthly ORDER BY warehouse_id, mth;

Date-function framework — the 6 verbs (write them without googling)

Every date task is one of six jobs. Name the job, the function follows — six verbs, not forty functions. DATE_TRUNC is the workhorse (identical in PostgreSQL / Snowflake / Spark); only ADD and DIFFERENCE differ enough to name your dialect.

"Daily delivered units per warehouse, including zero-activity days"

STEP A — BUILD the spine (grain = EVERY warehouse × EVERY day, even empty)
  warehouses {W1, W2}  CROSS JOIN  days {06-01, 06-02, 06-03}  →  6 rows, all exist

STEP B — LEFT JOIN shipments onto the spine (grain UNCHANGED: one wh-day)
  W1 06-01 qty=10 · W1 06-02 qty=NULL · W1 06-03 qty=5
  W2 06-01 qty=NULL · W2 06-02 qty=7 · W2 06-03 qty=NULL

── GROUP BY wh, day + COALESCE(SUM(qty),0) ──▸  ★ OUTPUT
  W1 06-01 10 · W1 06-02 0 · W1 06-03 5 · W2 06-01 0 · W2 06-02 7 · W2 06-03 0

WITH days AS (
  SELECT generate_series(DATE '2026-06-01', DATE '2026-06-03', INTERVAL '1 day')::date AS d
), grid AS (
  SELECT w.warehouse_id, d.d AS ship_date FROM warehouses w CROSS JOIN days d
)
SELECT g.warehouse_id, g.ship_date, COALESCE(SUM(s.qty), 0) AS units
FROM grid g
LEFT JOIN shipments s
       ON s.warehouse_id = g.warehouse_id
      AND s.ship_date   = g.ship_date
      AND s.status      = 'delivered'   -- filter on the JOIN, not WHERE
GROUP BY g.warehouse_id, g.ship_date ORDER BY g.warehouse_id, g.ship_date;

"Total revenue per warehouse" (and how a join doubles it)

SOURCE shipments (grain = one shipment)          ✓ correct
  SH1 wh1 100 · SH2 wh1 50        SUM per wh = 150  ✓

── JOIN shipment_events (one shipment has MANY events) ──▸  grain MULTIPLIES
  SH1 wh1 100 E1   ← revenue 100 repeated...
  SH1 wh1 100 E2   ← ...once per event
  SH2 wh1  50 E3
                    SUM per wh = 250  ✗  SH1's 100 counted TWICE

Fix: aggregate shipments before joining the second one-to-many table, or use COUNT(DISTINCT shipment_id), or join to a pre-aggregated subquery.

SELECT w.warehouse_id, w.warehouse_name, COALESCE(SUM(s.revenue), 0) AS total_rev
FROM warehouses w
LEFT JOIN shipments s ON s.warehouse_id = w.warehouse_id   -- one-to-many is fine ALONE
GROUP BY w.warehouse_id, w.warehouse_name ORDER BY total_rev DESC;