Skip to main content
PreparationZone Logo
PrepZone

Advanced SQL Patterns

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

Pass checkpoint below

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.

The rest of this guide is part of Pro

Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.

Still to come · 3 sections

  • Window functions
  • Running totals and top-N per group
  • Top-N per category

Test yourself

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