SQL — patterns I reach for

Window functions

-- Running total
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Rank within group
SELECT user_id, score,
       RANK() OVER (PARTITION BY cohort ORDER BY score DESC) AS rk
FROM students;

Recursive CTEs

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

Traversing a tree:

WITH RECURSIVE descendants AS (
    SELECT id, parent_id, name FROM categories WHERE id = 42
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

Pivot without PIVOT

SELECT
    user_id,
    SUM(CASE WHEN month = 1 THEN amount END) AS jan,
    SUM(CASE WHEN month = 2 THEN amount END) AS feb,
    SUM(CASE WHEN month = 3 THEN amount END) AS mar
FROM monthly_sales
GROUP BY user_id;