Intermediate · Lesson 10 of 14

Subqueries and CTEs

Build queries in named steps.

A subquery is a query inside a query

You can source rows from a subquery, filter against one with IN or EXISTS, and compute against scalar results like the max. It composes ideas without repeating logic.

CTEs are named subqueries

WITH totals AS (...) SELECT ... FROM totals reads like a story, reusable and testable one block at a time. Prefer them in interviews for readability.

Correlated subqueries

A subquery that references the outer row (NOT EXISTS with t.account_id = a.account_id) runs per outer row. It is the cleanest spelling of an anti-join.

Examples

Scalar subquery

Titles above the average runtime.

SELECT title, runtime_minutes FROM content WHERE runtime_minutes > (SELECT AVG(runtime_minutes) FROM content);

CTE step by step

Name a step, then use it.

WITH totals AS (SELECT user_id, SUM(minutes_watched) AS m FROM watch_events GROUP BY user_id) SELECT user_id, m FROM totals ORDER BY m DESC LIMIT 3;

Anti-join with NOT EXISTS

Users who never watched anything.

SELECT user_id FROM users u WHERE NOT EXISTS (SELECT 1 FROM watch_events w WHERE w.user_id = u.user_id);