Subqueries, CTEs, and Window Functions

Why Nested Subqueries Become a Problem

A subquery is a SELECT statement placed inside another SQL statement. Nesting subqueries is perfectly valid, but once you add two or three levels, the query becomes hard to read, hard to debug, and hard to hand off to a teammate. A correlated subquery—one that references columns from the outer query—re-executes for every row the outer query processes. On a large table this can be slow and the logic is often buried where reviewers miss it.

The goal of this lesson is to give you two tools that solve these problems cleanly: Common Table Expressions (CTEs) and window functions.

---

Common Table Expressions (CTEs)

A CTE is a named, temporary result set that lives only for the duration of the query. You define it with the `WITH` keyword before your main SELECT.

```sql

WITH monthly_sales AS (

SELECT

DATE_TRUNC('month', sale_date) AS month,

region,

SUM(amount) AS total

FROM sales_transactions

GROUP BY 1, 2

)

SELECT *

FROM monthly_sales

WHERE total > 50000;

```

The CTE `monthly_sales` is computed once, given a name, and then referenced like a table. Benefits over a nested subquery include:

• Readability – the logic is at the top, named, and self-documenting.
• Reusability – the same CTE name can appear multiple times in the main query.
• Debuggability – you can SELECT from the CTE alone while building the query.

You can chain multiple CTEs by separating them with commas after the WITH keyword.

```sql

WITH step1 AS ( ... ),

step2 AS (

SELECT * FROM step1 WHERE ...

)

SELECT * FROM step2;

```

---

Window Functions: Aggregating Without Collapsing Rows

A regular aggregate like `SUM()` with `GROUP BY` reduces many rows to one. A window function performs a calculation across a set of rows related to the current row but keeps every row in the output. This lets you see both the detail row and the aggregate answer at the same time—something impossible with GROUP BY alone.

The syntax uses the `OVER` clause:

```sql

function_name(column) OVER (

PARTITION BY partition_column

ORDER BY sort_column

ROWS/RANGE frame_specification

)

```

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing). ORDER BY inside OVER sets the row order within each partition.

Five Key Window Functions

ROW_NUMBER() assigns a unique sequential integer to each row within its partition, starting at 1. Ties receive different numbers—whichever row PostgreSQL processes first gets the lower number.

RANK() assigns the same rank to tied rows, then skips numbers. If two rows tie for rank 1, the next rank is 3.

LAG(column, n) returns the value of `column` from the row that is `n` rows before the current row in the partition order. Useful for period-over-period comparisons.

LEAD(column, n) does the same thing forward—the row `n` rows after the current row.

SUM() OVER (...) computes a running total or a partition total without collapsing rows.

---

Combining CTEs and Window Functions

The real power appears when you use a CTE to prepare clean data and then apply a window function in the main SELECT—or in a second CTE.

```sql

WITH permit_totals AS (

SELECT

contractor_id,

permit_category,

SUM(permit_value) AS total_value

FROM city_permits

GROUP BY contractor_id, permit_category

),

ranked AS (

SELECT

contractor_id,

permit_category,

total_value,

RANK() OVER (

PARTITION BY permit_category

ORDER BY total_value DESC

) AS category_rank

FROM permit_totals

)

SELECT

contractor_id,

permit_category,

total_value,

category_rank,

CASE WHEN category_rank = 1 THEN TRUE ELSE FALSE END AS top_performer

FROM ranked

ORDER BY permit_category, category_rank;

```

This pattern—aggregate in CTE, rank in second CTE or main SELECT, then flag—is the template for the lesson challenge query.

---

Data Accuracy and Safe Practice

Data analysts do not work with high-voltage equipment, but data safety is real and professionally serious.

• Never run a destructive statement (DELETE, UPDATE, DROP) on a shared or production database without a verified backup and explicit authorization. Treat shared datasets the way a lab technician treats a shared instrument: leave it in the same condition you found it.
• When testing window function queries, use a local copy or a dedicated practice schema so that no other student's work is affected.
• Validate query results before sharing them with stakeholders. A misread RANK() (mistaking it for ROW_NUMBER()) can produce an incorrect "top performer" that drives a real business decision. Review your output against a small, hand-checked sample.
• Version-control your `.sql` files. Saving query history protects you when a result is questioned later.

---

Quick Reference: Subquery vs. CTE

| Factor | Nested Subquery | CTE |

|---|---|---|

| Readability | Decreases with depth | Stays flat and named |

| Reuse in same query | Must repeat code | Reference by name |

| Debugging | Must extract manually | SELECT from CTE name |

| Performance | Varies; correlated can be slow | Optimizer treats similarly in PostgreSQL |

PostgreSQL's query planner generally handles both approaches, but CTEs in PostgreSQL are sometimes treated as optimization fences (the planner may not push predicates through them). Be aware of this when working with very large tables and test execution plans with `EXPLAIN ANALYZE`.