SQL CTE vs Subquery Guide

-- Subquery: written where it's used
SELECT columns FROM (SELECT ...) AS name;

-- CTE: named first, then used like a table
WITH name AS (SELECT ...)
SELECT columns FROM name;

SELECT n * 2 AS doubled FROM (SELECT 21 AS n) AS numbers;
-- Output: 42

WITH numbers AS (SELECT 21 AS n)
SELECT n * 2 AS doubled FROM numbers;
-- Output: 42

-- Input: people
-- first_name | last_name | job      | favorite_food
-- -----------+-----------+----------+--------------
-- Matthew    | Ortiz     | Chef     | cheese
-- Matt       | Novak     | Pilot    | pizza
-- Mattie     | Brooks    | Engineer | cheese
-- Priya      | Nair      | Teacher  | cheese

-- Subquery
SELECT *
FROM (SELECT first_name, last_name, job FROM people WHERE favorite_food = 'cheese') AS cheese_lovers
WHERE first_name LIKE 'Matt%';

-- CTE
WITH cheese_lovers AS (
  SELECT first_name, last_name, job FROM people WHERE favorite_food = 'cheese'
)
SELECT * FROM cheese_lovers
WHERE first_name LIKE 'Matt%';

-- Output of both:
-- first_name | last_name | job
-- -----------+-----------+---------
-- Matthew    | Ortiz     | Chef
-- Mattie     | Brooks    | Engineer

SELECT (SELECT 1 UNION ALL SELECT 2) AS n;
-- ERROR: more than one row returned by a subquery used as an expression
-- (SQLite doesn't complain: it quietly uses the first row, 1)

-- In SQL Server, leave out RECURSIVE
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 3
)
SELECT n FROM counter;

-- Output:
-- n
-- -
-- 1
-- 2
-- 3

-- Input: products
-- name          | category  | price
-- --------------+-----------+-------
-- Desk Lamp     | Lighting  | 24.99
-- Floor Lamp    | Lighting  | 89.00
-- Office Chair  | Furniture | 149.00
-- Standing Desk | Furniture | 399.00
-- Bookshelf     | Furniture | 120.00

SELECT name, category, price
FROM products p
WHERE price > (
  SELECT AVG(price) FROM products WHERE category = p.category
)
ORDER BY category, name;

-- Output:
-- name          | category  | price
-- --------------+-----------+-------
-- Standing Desk | Furniture | 399.00
-- Floor Lamp    | Lighting  | 89.00

-- Input: sales
-- id | product      | category  | units
-- ---+--------------+-----------+------
-- 1  | Desk Lamp    | Lighting  | 3
-- 2  | Floor Lamp   | Lighting  | 5
-- 3  | Desk Lamp    | Lighting  | 4
-- 4  | Office Chair | Furniture | 2
-- 5  | Bookshelf    | Furniture | 6

WITH product_totals AS (
  SELECT category, product, SUM(units) AS units
  FROM sales
  GROUP BY category, product
),
ranked AS (
  SELECT category, product, units,
         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS place
  FROM product_totals
)
SELECT category, product, units
FROM ranked
WHERE place = 1
ORDER BY category;

-- Output:
-- category  | product   | units
-- ----------+-----------+------
-- Furniture | Bookshelf | 6
-- Lighting  | Desk Lamp | 7

-- Input: orders
-- id | region | amount
-- ---+--------+--------
-- 1  | North  | 1200.00
-- 2  | South  | 450.00
-- 3  | North  | 300.00
-- 4  | West   | 800.00
-- 5  | South  | 250.00

WITH region_totals AS (
  SELECT region, SUM(amount) AS total
  FROM orders
  GROUP BY region
)
SELECT
  region,
  total,
  ROUND(100.0 * total / (SELECT SUM(total) FROM region_totals), 1) AS pct_of_sales
FROM region_totals
ORDER BY total DESC;

-- Output:
-- region | total   | pct_of_sales
-- -------+---------+-------------
-- North  | 1500.00 | 50.0
-- West   | 800.00  | 26.7
-- South  | 700.00  | 23.3

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2
-- 5  | Ana Diaz  | 1

-- In SQL Server, leave out RECURSIVE
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE name = 'Jo Kim'
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.level + 1
  FROM employees e
  JOIN chain ON e.id = chain.manager_id
)
SELECT level, name FROM chain ORDER BY level;

-- Output:
-- level | name
-- ------+----------
-- 0     | Jo Kim
-- 1     | Sam Ortiz
-- 2     | Maya Chen