All recipes
Cross-dialect SQL, grouped by topic.
Query
- Handle NULLs — COALESCE, NULLIF, and IS DISTINCT FROM3 min
- EXISTS vs IN vs JOIN for "has a matching row"3 min
- FULL OUTER JOIN (and emulating it in MySQL)3 min
- Run a subquery per row with LATERAL / CROSS APPLY3 min
- Select a random sample of rows3 min
- Fast pagination without OFFSET (keyset / seek method)3 min
Window functions
- Find gaps and consecutive runs (gaps and islands)3 min
- Running (cumulative) MIN and MAX2 min
- First and last value per group (FIRST_VALUE / LAST_VALUE)3 min
- Split rows into equal buckets (NTILE quartiles & deciles)2 min
- Compare a row to the previous one (LAG / LEAD)2 min
- Moving average (rolling window) in SQL2 min
- Percent of total (and percent of group)2 min
- ROW_NUMBER vs RANK vs DENSE_RANK2 min
- Running total (cumulative sum) in SQL3 min
- Top-N rows per group in SQL3 min
Grouping & aggregation
- Aggregate values into an array (ARRAY_AGG / JSON_ARRAYAGG)3 min
- Filter groups with HAVING (find duplicates & above-average groups)2 min
- Bucket values into a histogram3 min
- Weighted average: SUM(x*w)/SUM(w)2 min
- Subtotals and grand totals with ROLLUP3 min
- Conditional count and sum (COUNT/SUM with a condition)2 min
- Concatenate rows into one string per group3 min
- Median and percentiles in SQL3 min
- Pivot rows into columns3 min
- Unpivot columns into rows3 min
Insert, update & upsert
JSON
Strings
CTEs & recursion