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

The outputs on this page come from a re-run on 14 November 2026 in an Ubuntu 24.04 container with PostgreSQL 16 (arm64). Sample files, users, and hosts were created for the run, so names, numbers, and timestamps reflect that run.

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;

Output (first 6 rows):

 customer_id |    t    | id |     name     | region
-------------+---------+----+--------------+--------
           1 | 7470.00 |  1 | Customer 001 | West
           2 | 8112.00 |  2 | Customer 002 | North
           3 | 7804.00 |  3 | Customer 003 | South
           4 | 8396.00 |  4 | Customer 004 | East
           5 | 8088.00 |  5 | Customer 005 | West
           6 | 7780.00 |  6 | Customer 006 | North

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;

Output (first 8 rows):

     name     | row_number
--------------+------------
 Customer 004 |          1
 Customer 016 |          2
 Customer 028 |          3
 Customer 008 |          4
 Customer 020 |          5
 Customer 012 |          6
 Customer 024 |          7
 Customer 032 |          8

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;

Output:

                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=16.48..16.49 rows=5 width=44) (actual time=0.202..0.204 rows=5 loops=1)
   ->  Sort  (cost=16.48..16.57 rows=38 width=44) (actual time=0.201..0.202 rows=5 loops=1)
         Sort Key: t.t DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Hash Join  (cost=14.33..15.85 rows=38 width=44) (actual time=0.164..0.172 rows=38 loops=1)
               Hash Cond: (c.id = t.customer_id)
               ->  Seq Scan on customer c  (cost=0.00..1.40 rows=40 width=16) (actual time=0.006..0.009 rows=40 loops=1)
               ->  Hash  (cost=13.86..13.86 rows=38 width=36) (actual time=0.148..0.149 rows=38 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 10kB
                     ->  Subquery Scan on t  (cost=13.00..13.86 rows=38 width=36) (actual time=0.131..0.139 rows=38 loops=1)
                           ->  HashAggregate  (cost=13.00..13.48 rows=38 width=36) (actual time=0.131..0.136 rows=38 loops=1)
                                 Group Key: invoice.customer_id
                                 Batches: 1  Memory Usage: 32kB
                                 ->  Seq Scan on invoice  (cost=0.00..10.00 rows=600 width=9) (actual time=0.004..0.038 rows=600 loops=1)
 Planning Time: 0.481 ms
 Execution Time: 0.266 ms
(16 rows)

What this teaches: EXPLAIN ANALYZE runs the query and reports actual rows and time per node, not estimates. Read bottom up: the Seq Scan on invoice feeds the HashAggregate (on a table this small, scanning beats an index), 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);

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.

Frequently Asked Questions

How do I fix 'row counts explode after a join'?

Likely cause: a many-to-many fan-out duplicated rows. Fix: aggregate before joining, or check join keys for duplicates.

How do I fix 'LEFT JOIN dropped rows anyway'?

Likely cause: a WHERE clause on the right table nullified the outer join. Fix: move right-table filters into the ON clause.

How do I fix 'window function ranks globally'?

Likely cause: PARTITION BY was omitted. Fix: add PARTITION BY for per-group ranking; omit it only for global ranking.

How do I fix 'CTE is slow'?

Likely cause: the CTE is referenced twice and recomputed, or it blocks predicate pushdown. Fix: on PostgreSQL 12+ CTEs inline by default; check the plan for double evaluation.

How do I fix 'EXPLAIN estimates wildly off'?

Likely cause: stale table statistics. Fix: run ANALYZE on the tables and re-check the plan.

Cite This Page

Citation formats

Plain text

Taki Sadik, "SQL JOINs, CTEs, and Window Functions", TYFSADIK, https://tyfsadik.org/blog/labs/databases/joins-complex-queries (accessed 2026-10-22).

BibTeX

@misc{sadik-joins-complex-queries,
  author  = {Sadik, MD. Taki Yasir Faraji},
  title   = {SQL JOINs, CTEs, and Window Functions},
  year    = {2026},
  url     = {https://tyfsadik.org/blog/labs/databases/joins-complex-queries},
  urldate = {2026-10-22}
}

APA

Sadik, M. T. Y. F. (2026). SQL JOINs, CTEs, and Window Functions. TYFSADIK. https://tyfsadik.org/blog/labs/databases/joins-complex-queries