All recipes

Window functions

• 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

• 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

• Delete duplicate rows, keeping one3 min
• UPDATE a table from a join3 min
• Upsert (insert or update) in SQL3 min

JSON

• Expand a JSON array into rows3 min
• Read a field from a JSON column3 min

Strings

• Split a delimited string into rows3 min

CTEs & recursion

• Generate a sequence of numbers2 min
• Walk a hierarchy with a recursive CTE3 min