The SQL a Data PM Actually Uses

You are not writing production pipelines. You are verifying numbers, sizing problems, and reading other people's queries. That needs a small, sharp toolkit.

Shapes you'll write every week

• CTEs (WITH x AS (...)) — one step per CTE, named after what it produces (resolved_deals, latest_stage). Readable beats clever.
• Window functions — ROW_NUMBER() OVER (PARTITION BY deal_id ORDER BY entered_at DESC) for "latest per entity"; LAG/LEAD for change between rows; SUM(...) OVER (ORDER BY month) for running totals; AVG(...) OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for moving averages.
• Date bucketing — substr(date, 1, 7) for month, strftime('%Y-%W', ts) for ISO-ish week, julianday(b) - julianday(a) for day differences (SQLite).
• Conditional aggregation — SUM(CASE WHEN ... THEN 1 ELSE 0 END) (or SUM(condition) in SQLite) to compute several rates in one pass.
• HAVING — filter after aggregation; the idiom for duplicates: GROUP BY key HAVING COUNT(*) > 1.

The four silent metric bugs

1. Join fan-out — joining a 1:N table before an aggregate multiplies the measure. Aggregate the N side first.
2. LEFT JOIN + WHERE on the right table — turns it into an inner join and hides exactly the missing rows you were looking for. Put the condition in ON.
3. COUNT(*) vs COUNT(DISTINCT x) — views vs viewers, rows vs people.
4. Integer division — 7 / 10 = 0 in many engines. Multiply by 1.0 first.

Grain first, window second

Decide what one row means (a deal? a month?), aggregate to that grain in a CTE, then apply windows. Running totals and moving averages on the wrong grain look plausible and are wrong.