Preview — Pro guide
You are seeing a portion of this guide. Sign in and upgrade to unlock the full article, quizzes, and interview answers.
Sections
Related Guides
SQL Joins & Aggregations: INNER, LEFT, GROUP BY, and Aggregation Pitfalls
SQL & Databases
Window Functions for Analytics: ROW_NUMBER, RANK, LAG/LEAD, and Running Totals
SQL & Databases
SQL Query Optimization: Indexes, Query Plans, and Performance at Scale
SQL & Databases
SQL Indexes and Query Performance: B-Tree, Composite, and Covering Indexes
SQL & Databases
Subqueries and CTEs: WITH Clauses, Correlated Subqueries, and Recursive Patterns
Subqueries and CTEs are the SQL constructs that separate mid-level analysts from senior engineers. This guide covers scalar subqueries, correlated subqueries and their performance implications, the WITH (CTE) clause for readable multi-step logic, and recursive CTEs for hierarchical data — all tested at Meta, Stripe, Google, and Airbnb's data rounds.
Why CTEs and Subqueries Separate Mid from Senior SQL
Subqueries are SQL expressions embedded inside other SQL statements. CTEs (Common Table Expressions) are named subqueries declared before the main query using WITH. Together they allow multi-step data transformations in a single SQL statement.
The critical distinction between sub-query types:
- Scalar subquery: returns exactly one row and one column; usable anywhere a single value is expected (SELECT list, WHERE, HAVING)
- Inline view / derived table: returns multiple rows and columns; used in FROM clause like a regular table; executed once
- Correlated subquery: references a column from the outer query; re-executed once per outer row — can be slow at scale
- CTE: a named inline view at the top of the query; improves readability; most optimizers treat it identically to an inline view (not a materialized temp table by default)
At Meta, Stripe, and Airbnb, hard SQL questions almost always require 2–4 CTEs chained together. The ability to break a multi-step problem into named intermediate results is the distinguishing skill.