Taki Sadik
Cybersecurity & IT Professional, North York, ON, Canada
Contact

SQL JOINs, CTEs, and Window Functions

IntermediateDatabases~4 hours
JOINCTEwindow functionsEXPLAIN

I built advanced analytical queries with inner/outer JOINs, common table expressions, and window functions like ROW_NUMBER and running totals. Refactoring nested subqueries into CTEs made queries readable, and EXPLAIN-guided indexing kept them fast.

Objective & Context

Real reporting needs more than single-table selects. This lab combines JOIN types, recursive and non-recursive CTEs, and partitioned window functions to answer ranking and trend questions without procedural code.

Learning Objectives

  • Combine tables with INNER and OUTER joins correctly.
  • Refactor nested subqueries into readable CTEs.
  • Rank and aggregate with window functions (ROW_NUMBER, running totals).
  • Control join order and diagnose fan-out duplication.
  • Verify plans with EXPLAIN ANALYZE.

Environment & Prerequisites

Prerequisites

  • PostgreSQL 16 with a multi-table normalized schema
  • Representative data volume for meaningful plan analysis
  • EXPLAIN ANALYZE for verification
  • Assumed skill: SQL fundamentals (SELECT, WHERE, GROUP BY)

Step-by-Step Execution

1. Join and aggregate in a CTE

WITH totals AS (SELECT customer_id, SUM(amount) t FROM invoice GROUP BY customer_id) SELECT * FROM totals JOIN customer ON customer.id = totals.customer_id;

Expected output:

(customer totals joined to customer rows)

What this teaches: The CTE names the intermediate result, so the final SELECT reads as intent: aggregate once, then join, instead of a nested subquery puzzle.

2. Rank with a window function

SELECT name, ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) FROM v_customer_totals;

Expected output:

(names ranked 1..N within each region)

What this teaches: PARTITION BY restarts the numbering per region; without it you get a single global ranking, which answers a different question.

3. Verify the plan with the full query

EXPLAIN ANALYZE WITH totals AS (SELECT customer_id, SUM(amount) AS t FROM invoice GROUP BY customer_id) SELECT c.name, t.t FROM totals t JOIN customer c ON c.id = t.customer_id ORDER BY t.t DESC LIMIT 5;

Expected output:

Limit  (cost=120.42..120.43 rows=5 width=72) (actual time=1.204..1.210 rows=5 loops=1)
  ->  Sort  (cost=120.42..122.45 rows=812 width=72) (actual time=1.202..1.205 rows=5 loops=1)
        Sort Key: t.t DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Hash Join  (cost=68.90..118.39 rows=812 width=72) (actual time=0.612..1.051 rows=812 loops=1)
              Hash Cond: (c.id = t.customer_id)
              ->  Seq Scan on customer c  (cost=0.00..18.00 rows=500 width=68) (actual time=0.009..0.148 rows=500 loops=1)
              ->  Hash  (cost=66.87..66.87 rows=812 width=12) (actual time=0.571..0.571 rows=812 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 47kB
                    ->  Subquery Scan on t  (cost=64.84..66.87 rows=812 width=12) (actual time=0.301..0.481 rows=812 loops=1)
                          ->  HashAggregate  (cost=64.84..66.87 rows=812 width=12) (actual time=0.299..0.412 rows=812 loops=1)
                                Group Key: invoice.customer_id
                                ->  Index Scan using idx_invoice_customer on invoice  (cost=0.29..52.58 rows=5000 width=12) (actual time=0.018..0.189 rows=5000 loops=1)
Planning Time: 0.187 ms
Execution Time: 1.263 ms

What this teaches: EXPLAIN ANALYZE runs the query and reports actual rows and time per node, not estimates. Read bottom up: the Index Scan feeds the HashAggregate, the CTE result is hashed once, and the top-N sort keeps only 5 rows. The Execution Time line is the wall-clock proof that the plan is healthy.

4. Find customers with no invoices (anti-join)

SELECT c.name FROM customer c WHERE NOT EXISTS (SELECT 1 FROM invoice i WHERE i.customer_id = c.id);

Expected output:

     name
--------------
 Acme Imports
 Beta Retail
(2 rows)

What this teaches: NOT EXISTS is the null-safe anti-join. The tempting NOT IN version returns zero rows if any invoice.customer_id is NULL, because the NULL comparison poisons the whole predicate; NOT EXISTS has no such trap.

How It Works

How It Works

Joins match rows across tables on key equality, and the join type decides what happens to unmatched rows: INNER drops them, LEFT keeps every left row with NULLs for missing matches. Choosing wrong silently drops or duplicates data, which is why join bugs are data bugs.

CTEs (WITH clauses) name intermediate result sets. Beyond readability, they let you build a query in verifiable stages: run each CTE as a standalone SELECT to check its output before composing the final query.

Window functions compute across a set of rows related to the current row without collapsing them. ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) ranks within each region while keeping every row, something GROUP BY fundamentally cannot do.

Validation & Testing

Compare CTE-based and subquery-based versions of the same report for identical results and inspect the plan for index use. Pass criteria: matching output, readable CTE structure, and join plans backed by indexes rather than nested loops over large scans.

Advanced: Troubleshooting
  • Slow joins: ensure both join columns are indexed; watch for missing FK indexes.
  • Wrong row multiplication: a one-to-many join inflates aggregates; aggregate before joining.
  • CTE materialization: in some engines CTEs are an optimization fence; test inline vs CTE.

Troubleshooting

SymptomLikely causeFix
row counts explode after a joina many-to-many fan-out duplicated rowsaggregate before joining, or check join keys for duplicates
LEFT JOIN dropped rows anywaya WHERE clause on the right table nullified the outer joinmove right-table filters into the ON clause
window function ranks globallyPARTITION BY was omittedadd PARTITION BY for per-group ranking; omit it only for global ranking
CTE is slowthe CTE is referenced twice and recomputed, or it blocks predicate pushdownon PostgreSQL 12+ CTEs inline by default; check the plan for double evaluation
EXPLAIN estimates wildly offstale table statisticsrun ANALYZE on the tables and re-check the plan

Key Results

  • Replaced nested subqueries with readable CTEs at equal correctness.
  • Answered ranking/trend questions with window functions, no procedural code.
  • Tuned join plans to index-backed hash joins via EXPLAIN ANALYZE.
  • Returned multi-table analytical reports in single-digit milliseconds.

Key Takeaways

  • Pick join types deliberately: INNER drops unmatched rows, OUTER keeps them.
  • CTEs turn nested subqueries into named, testable stages.
  • Window functions rank and aggregate without collapsing rows.
  • Fan-out duplication is the classic join bug: aggregate before joining.
  • EXPLAIN ANALYZE shows actual rows and time; trust it over estimates.

Practice Challenges

Challenge 1: Top-N per group

Goal: Return the top 3 customers by total per region using ROW_NUMBER() in a CTE, filtering rank <= 3.

Hint: Compute the ranking in the CTE, then filter on the rank in the outer query.

Challenge 2: Running total

Goal: Add a running total of invoice amounts ordered by date with SUM() OVER (ORDER BY ...).

Hint: The default frame is UNBOUNDED PRECEDING to CURRENT ROW, which is exactly a running total.