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
Subqueries in WHERE and FROM
Filter using a computed set:
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:
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.