The Query Cookbook: Day-to-Day Patterns
Eleven query patterns that come up constantly in real analytics work — a reference to copy from, not a tutorial to read start to finish.
Eleven recipes, each self-contained (full code in the companion repo):
- Top-N per group —
ROW_NUMBER() OVER (PARTITION BY ...), filtered in an outer query. - EXISTS vs. IN vs. NOT EXISTS —
NOT INsilently returns zero rows if the subquery contains one NULL; preferNOT EXISTS. - UPSERT —
INSERT ... ON CONFLICT DO UPDATE(or platform-nativeMERGE— see Part 8). - Pivot / cross-tab —
SUM(CASE WHEN ... THEN ... END), since standard SQL has no nativePIVOT. - Month-over-month change — aggregate to one row per period (a CTE), then
LAG()over it. - Moving average —
AVG(...) OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW).
The Full Eleven
Top-N per group · Nth highest value · find/remove duplicates · EXISTS vs. IN vs. NOT EXISTS · UPSERT · NULL handling (COALESCE/NULLIF) · pivot/cross-tab · month-over-month change · percent of total · INTERSECT/EXCEPT · moving average. Full runnable code for every recipe is in the companion repo's Part 7 notebook. Top-N per group — the featured recipe below — answers a question a plain GROUP BY genuinely can't: "the top 2 highest-priced products, within each category," not the single overall top 2 across everything. GROUP BY can compute the maximum price per category, but it can't return the top-2 rows per category, because grouping collapses rows instead of ranking and keeping them. The fix is ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) (Part 4) to number each row within its own category, then filtering that number in an outer query.
SQL · Recipe 1, Top-N per group
-- Top 2 highest-priced products, WITHIN each category
WITH ranked AS (
SELECT p.category, p.name, p.price,
ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY p.price DESC) AS rn
FROM products p
)
SELECT category, name, price
FROM ranked
WHERE rn <= 2
ORDER BY category, price DESC;
-- Recipe 5, UPSERT in one statement
INSERT INTO products (id, name, category, price, stock_quantity)
VALUES (21, 'Wireless Mouse', 'Electronics', 24.99, 100)
ON CONFLICT(id) DO UPDATE SET stock_quantity = stock_quantity + 100;The NOT IN Trap
"Rows that don't have a match in another table" (customers with no orders, products never sold) is one of the most common analytics questions, and the intuitive way to write it — WHERE x NOT IN (SELECT y FROM ...) — hides a real trap: if that subquery's result contains even a single NULL, the entire NOT IN comparison silently returns zero rows, for every row, with no error. This happens because NOT IN expands internally to a series of != comparisons ANDed together, and anything != NULL evaluates to NULL rather than true, which poisons the whole condition. NOT EXISTS asks a logically equivalent question a different way (does any matching row exist at all) and never runs into this trap, which is why it should be the default choice over NOT IN whenever the subquery's column could ever contain a NULL.
SQL · Recipe 4, the safe pattern
-- Customers who have never placed an order
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Percent of Total, No Self-Join
"What share of total revenue does each category represent" needs two numbers per row: that category's own revenue, and the grand total across every category, to divide by. Without window functions, getting the grand total onto every row would mean a self-join (joining the aggregated table back to itself) or a separate query whose result gets glued on afterward — both more moving parts than the question deserves. A window SUM(...) OVER () with a completely empty OVER () — no PARTITION BY, no ORDER BY — computes the sum across the entire result set and repeats that same total on every single row, so each row can divide its own value by it directly, in the same SELECT, no self-join required.
SQL · Recipe 9, percent of total
SELECT p.category,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS category_revenue,
ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
/ SUM(SUM(oi.quantity * oi.unit_price)) OVER (), 1) AS pct_of_total
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY pct_of_total DESC;UNION (not shown separately) is UNION ALL plus a de-duplication pass — same stacking idea, extra cost.
Recognize the shape of the question — the recipe follows.
The takeaway: These eleven patterns cover the large majority of "how do I write this query" questions that come up in real analytics work — the goal is recognition (this is a top-N problem, this is a percent-of-total problem), not memorization.
Runnable code for this part: sql-for-data-platforms on GitHub — Module 7 of 14.
← Back to Publications