Lesson 8 of 12 · 0 of 2 exercises solved

Subqueries & CTEs

When one query needs the answer to another query, you have two options: nest it as a , or name it up front as a CTE. CTEs read top to bottom, which makes longer queries easier to follow.

-- Scalar subquery: returns exactly one value
SELECT name, price,
       (SELECT AVG(price) FROM products) AS avg_price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN: does this value appear in that result set?
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);

-- EXISTS: usually faster, and it stops at the first match
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.total > 500
);

-- NOT EXISTS: customers who have never ordered
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Derived table: a subquery in the FROM clause (it needs an alias)
SELECT region, order_count
FROM (
    SELECT region, COUNT(*) AS order_count
    FROM orders
    GROUP BY region
) AS totals
WHERE order_count > 10;

SQL

-- A CTE names a result set so you can read the query in order.
WITH monthly_sales AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
customer_totals AS (
    SELECT customer_id, SUM(revenue) AS lifetime_value
    FROM monthly_sales
    GROUP BY customer_id
),
tiers AS (
    SELECT
        customer_id,
        lifetime_value,
        CASE
            WHEN lifetime_value >= 10000 THEN 'platinum'
            WHEN lifetime_value >=  2000 THEN 'gold'
            ELSE 'standard'
        END AS tier
    FROM customer_totals
)
SELECT c.name, t.tier, t.lifetime_value
FROM tiers t
JOIN customers c ON c.id = t.customer_id
WHERE t.tier <> 'standard'
ORDER BY t.lifetime_value DESC;

SQL

-- Recursive CTEs walk hierarchies: org charts, category trees, graphs.
WITH RECURSIVE org_chart AS (
    -- Anchor: where the walk starts
    SELECT id, name, manager_id, 1 AS depth, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: joins back onto the CTE itself
    SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.path || ' > ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE oc.depth < 10                 -- always bound the recursion
)
SELECT depth, path
FROM org_chart
ORDER BY path;

-- Generating a series of dates to fill gaps in a report
WITH RECURSIVE days AS (
    SELECT DATE '2026-01-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day' FROM days WHERE day < DATE '2026-01-31'
)
SELECT d.day, COALESCE(SUM(o.total), 0) AS revenue
FROM days d
LEFT JOIN orders o ON DATE(o.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;

Practice

Return the names of customers who have placed at least one order with a total above 500. Use IN or EXISTS with a subquery, not a JOIN.

Exercise

Subquery filter

2 checks

SELECT name FROM customers WHERE ...;

Output

Press Run to try your code, then Check answer to grade it.

Using a CTE named customer_totals, compute each customer's total spending, then return name and total_spent for everyone who has spent more than 300, highest first.

Exercise

A CTE for readability

3 checks

WITH customer_totals AS (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT ...

Output

Press Run to try your code, then Check answer to grade it.

Finished reading?2 exercises still open. You can come back to them later.

Tip: use the ← and → keys to move between lessons.