All recipes

Query

• Handle NULLs — COALESCE, NULLIF, and IS DISTINCT FROM 3 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 APPLY 3 min →
• Select a random sample of rows 3 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 MAX 2 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 SQL 2 min →
• Percent of total (and percent of group) 2 min →
• ROW_NUMBER vs RANK vs DENSE_RANK 2 min →
• Running total (cumulative sum) in SQL 3 min →
• Top-N rows per group in SQL 3 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 histogram 3 min →
• Weighted average: SUM(x*w)/SUM(w) 2 min →
• Subtotals and grand totals with ROLLUP 3 min →
• Conditional count and sum (COUNT/SUM with a condition) 2 min →
• Concatenate rows into one string per group 3 min →
• Median and percentiles in SQL 3 min →
• Pivot rows into columns 3 min →
• Unpivot columns into rows 3 min →

Insert, update & upsert

• Conditional UPDATE with CASE 2 min →
• Keep only the latest row per group (dedupe with ROW_NUMBER) 3 min →
• DELETE rows using a join to another table 3 min →
• Delete duplicate rows, keeping one 3 min →
• UPDATE a table from a join 3 min →
• Upsert (insert or update) in SQL 3 min →

JSON

• Build JSON from rows (aggregate rows into a JSON array/object) 3 min →
• Expand a JSON array into rows 3 min →
• Read a field from a JSON column 3 min →

Strings

• Get the Nth part of a delimited string 3 min →
• Case-insensitive search (ILIKE / LOWER / collation) 3 min →
• Split a delimited string into rows 3 min →

CTEs & recursion

• Generate a sequence of numbers 2 min →
• Walk a hierarchy with a recursive CTE 3 min →