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.

Environment & Prerequisites

  • PostgreSQL 16 with a multi-table normalized schema.
  • Representative data volume for plan analysis.
  • EXPLAIN ANALYZE for verification.

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 USING (id);

2. Rank with a window function

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

3. Verify the plan

EXPLAIN ANALYZE SELECT ...;
Hash Join (actual rows=812 time=2.1ms)  using idx_invoice_customer

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.

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.

Related Labs