Advanced SQL Patterns

Subqueries, CTEs, and window functions for rankings, running totals, and cohort analysis.

Intermediate11 min read

Read these first

Query Optimization and EXPLAIN

Why this matters

Window functions replace fragile application-side loops for rankings and running totals.
CTEs improve readability of multi-step reports that would otherwise be nested subquery soup.
Interview SQL rounds increasingly test ROW_NUMBER, RANK, and cohort retention queries.
Pushing analytics to SQL keeps result sets consistent with transactional data in Postgres.

Clustered (PK)

Table rows (sorted by PK)

Row 1id=1

Row 2id=2

Row 3id=3

Non-clustered (email)

email index→ row pointer

Table heapunordered rows

A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

Subqueries in WHERE and FROM

Filter using a computed set:

Java

SELECT id, name, price_cents
FROM products
WHERE id IN (
    SELECT product_id
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.created_at >= now() - INTERVAL '30 days'
    GROUP BY product_id
    HAVING SUM(oi.quantity) > 100
);

Prefer JOINs when the planner optimises them better — always EXPLAIN both forms on large data.

Common Table Expressions (CTEs)

CTEs name intermediate result sets for clarity:

Java

WITH paid_orders AS (
    SELECT id, customer_id, total_cents, created_at
    FROM orders
    WHERE status = 'PAID'
      AND created_at >= '2026-01-01'
),
customer_totals AS (
    SELECT customer_id, SUM(total_cents) AS lifetime_spend
    FROM paid_orders
    GROUP BY customer_id
)
SELECT c.email, ct.lifetime_spend
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
ORDER BY ct.lifetime_spend DESC
LIMIT 50;

VaultCommerce analysts read top-to-bottom — each CTE documents one logical step. Recursive CTEs traverse category trees (parent_id hierarchies) for breadcrumb navigation.

Window functions

Window functions compute across related rows without collapsing groups like GROUP BY:

Java

SELECT
    p.name,
    p.category_id,
    SUM(oi.quantity) AS units_sold,
    RANK() OVER (
        PARTITION BY p.category_id
        ORDER BY SUM(oi.quantity) DESC
    ) AS rank_in_category
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'PAID'
GROUP BY p.id, p.name, p.category_id;

PARTITION BY defines groups; ORDER BY inside OVER defines ranking order within each partition.

Running totals and top-N per group

Running revenue uses SUM(...) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Merchandising plots cumulative Black Friday totals from that pattern.

Top-N per category

Fetch the three bestselling SKUs per category without a correlated subquery:

Java

WITH ranked AS (
    SELECT
        p.category_id,
        p.sku,
        SUM(oi.quantity) AS units,
        ROW_NUMBER() OVER (
            PARTITION BY p.category_id
            ORDER BY SUM(oi.quantity) DESC
        ) AS rn
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category_id, p.sku
)
SELECT category_id, sku, units
FROM ranked
WHERE rn <= 3;

ROW_NUMBER assigns unique ranks; RANK allows ties with gaps; DENSE_RANK allows ties without gaps. Cohort retention uses the same CTE pattern — group customers by first-order month, then join back to measure repeat activity.

Quick recall

Everything you need if you only revisit this box.

Subqueries filter or derive inline result sets; JOINs often perform better — verify with EXPLAIN.
CTEs (WITH) structure multi-step reports for readability.
Window functions use OVER (PARTITION BY ... ORDER BY ...) for rankings and running totals.
ROW_NUMBER, RANK, and DENSE_RANK differ in tie handling.
Top-N per group and cohort queries are standard advanced SQL interview patterns.

Test yourself

Answer these before moving on — recall is what makes it stick.