All recipes

Window functions

• 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

• 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

• Delete duplicate rows, keeping one 3 min →
• UPDATE a table from a join 3 min →
• Upsert (insert or update) in SQL 3 min →

JSON

• Expand a JSON array into rows 3 min →
• Read a field from a JSON column 3 min →

Strings

• 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 →