SQL Patterns I Use Every Week

I’m not going to explain SELECT * or WHERE clauses. If you’re reading this blog, you’ve got the basics. What I want to share are the patterns that took me embarrassingly long to internalize — the ones I now reach for automatically when a data problem shows up.

All examples are PostgreSQL-flavored, but most work in any modern SQL dialect with minor tweaks.

1. Window Functions: Running Totals and Rankings

If you’re still doing self-joins to calculate running totals, window functions will change your life. They perform calculations across a set of rows related to the current row — without collapsing them into a single aggregate like GROUP BY does.

Running total

SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_revenue
ORDER BY order_date;

Rank within a group

-- Top 3 customers by revenue per country
SELECT *
FROM (
    SELECT
        customer_id,
        country,
        total_revenue,
        RANK() OVER (PARTITION BY country ORDER BY total_revenue DESC) AS country_rank
    FROM customer_revenue
) ranked
WHERE country_rank <= 3;

PARTITION BY is the key — it resets the window for each group. Here, ranking restarts fresh for every country.

Row-over-row change

SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change,
    ROUND(
        100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
        1
    ) AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

The NULLIF(..., 0) prevents divide-by-zero when the previous month was zero.

---

2. CTEs: Write SQL You Can Actually Read Later

A Common Table Expression (WITH clause) lets you name intermediate results and refer to them like tables. The query reads top-to-bottom instead of inside-out.

Before CTEs (nested subquery hell)

SELECT customer_id, avg_order_value
FROM (
    SELECT customer_id, AVG(order_total) AS avg_order_value
    FROM (
        SELECT o.customer_id, o.id AS order_id,
               SUM(li.unit_price * li.quantity) AS order_total
        FROM orders o
        JOIN line_items li ON li.order_id = o.id
        WHERE o.status = 'completed'
        GROUP BY o.customer_id, o.id
    ) order_totals
    GROUP BY customer_id
) customer_avgs
WHERE avg_order_value > 100;

After CTEs (same logic, readable)

WITH order_totals AS (
    SELECT
        o.customer_id,
        o.id AS order_id,
        SUM(li.unit_price * li.quantity) AS order_total
    FROM orders o
    JOIN line_items li ON li.order_id = o.id
    WHERE o.status = 'completed'
    GROUP BY o.customer_id, o.id
),
customer_avgs AS (
    SELECT customer_id, AVG(order_total) AS avg_order_value
    FROM order_totals
    GROUP BY customer_id
)
SELECT customer_id, avg_order_value
FROM customer_avgs
WHERE avg_order_value > 100;

Same result. Six months from now, you’ll still understand the CTE version.

---

3. FILTER Clause: Conditional Aggregates Without Pivoting

Instead of multiple subqueries or a messy CASE WHEN inside every aggregate, FILTER lets you apply different conditions to different aggregations in one pass.

-- Order counts and revenue by status, all in one query
SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    SUM(total) FILTER (WHERE status = 'completed') AS completed_revenue,
    SUM(total) FILTER (WHERE status = 'refunded') AS refunded_amount
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';

One table scan. Clean output. No PIVOT gymnastics.

---

4. Identifying Gaps in Sequential Data

Classic problem: you have a sequence of IDs or dates, and you need to find the gaps. Where are the missing order numbers? Which days had no transactions?

Find missing IDs in a sequence

WITH expected AS (
    SELECT generate_series(
        (SELECT MIN(id) FROM orders),
        (SELECT MAX(id) FROM orders)
    ) AS expected_id
)
SELECT expected_id AS missing_id
FROM expected
LEFT JOIN orders ON orders.id = expected.expected_id
WHERE orders.id IS NULL
ORDER BY expected_id;

Find days with no activity

WITH date_series AS (
    SELECT generate_series(
        '2026-01-01'::date,
        CURRENT_DATE,
        INTERVAL '1 day'
    )::date AS day
)
SELECT ds.day
FROM date_series ds
LEFT JOIN orders o ON o.created_at::date = ds.day
WHERE o.id IS NULL
ORDER BY ds.day;

generate_series is Postgres-specific but extraordinarily useful for this pattern.

---

5. Upsert (INSERT … ON CONFLICT)

Idempotent loads are the foundation of reliable ETL. If your pipeline runs twice, the result should be the same as running it once. ON CONFLICT makes that possible at the database level.

-- Insert new records; update existing ones if they've changed
INSERT INTO contacts (id, email, name, company, updated_at)
VALUES
    (1, '[email protected]', 'Alice Smith', 'Acme Corp', NOW()),
    (2, '[email protected]', 'Bob Jones', 'Globex', NOW())
ON CONFLICT (id) DO UPDATE SET
    email      = EXCLUDED.email,
    name       = EXCLUDED.name,
    company    = EXCLUDED.company,
    updated_at = EXCLUDED.updated_at
WHERE contacts.updated_at < EXCLUDED.updated_at;

The WHERE clause at the end is the key: it only updates if the incoming record is newer than what’s already stored. This prevents a re-run from clobbering a record that was manually corrected after the original load.

---

6. Pivoting Rows to Columns

Sometimes you need to turn a (key, value) table into wide-format columns. Standard SQL doesn’t have a native PIVOT, but FILTER or CASE WHEN inside aggregates gets you there.

-- Source: events(user_id, event_name, event_date)
-- Goal: one row per user with columns for first occurrence of each event

SELECT
    user_id,
    MIN(event_date) FILTER (WHERE event_name = 'signup')    AS first_signup,
    MIN(event_date) FILTER (WHERE event_name = 'purchase')  AS first_purchase,
    MIN(event_date) FILTER (WHERE event_name = 'churn')     AS first_churn,
    COUNT(*) FILTER (WHERE event_name = 'purchase')         AS total_purchases
FROM events
GROUP BY user_id;

Combine with a window function for “days between signup and first purchase” and you’ve got a full funnel analysis in one query.

---

7. Recursive CTEs for Hierarchical Data

Org charts, category trees, parent-child relationships — whenever data references itself, recursive CTEs are how you walk the hierarchy.

-- Walk an org chart from the top down
WITH RECURSIVE org_tree AS (
    -- Anchor: start with the CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: join employees to their managers
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    REPEAT('  ', depth) || name AS org_chart,
    depth
FROM org_tree
ORDER BY depth, name;

Add WHERE depth <= 3 to limit how deep you walk. Add a path column to detect cycles if your data might have them.

---

The Pattern Behind the Patterns

Look at all of these together and you’ll notice a theme: they all favor expressing intent clearly over being clever. A window function says “calculate this within a group” rather than hiding that logic in a self-join. A CTE says “here’s what I’m calling this intermediate result” instead of burying it in a subquery.

Good SQL reads like good writing. Each clause should be doing one clear thing.

The more SQL you write, the more you realize it’s not a query language — it’s a data transformation language. And these patterns are the vocabulary.

— Matthew