Within a single table, comparing different periods or states—rows that satisfy condition A and also condition B—is not always straightforward.

In such cases, an intuitive and effective approach is to build one CTE for the list satisfying condition A, another for the list satisfying condition B, and JOIN them to find the intersection.

WITH set_a AS (
  SELECT DISTINCT id FROM events WHERE condition_a
),
set_b AS (
  SELECT DISTINCT id FROM events WHERE condition_b
)
SELECT a.id
FROM set_a a
JOIN set_b b ON a.id = b.id;

From the orders table, extract the user_id values of repeat customers who purchased in both January and February 2024.

1. CTE jan_users: Select the user_id values that placed orders in January (remove duplicates).
2. CTE feb_users: Select the user_id values that placed orders in February (remove duplicates).
3. Main query: INNER JOIN both CTEs and return the user_id values present in both months.

-- 1. January customers
WITH jan_users AS (
  SELECT DISTINCT user_id  -- one row per customer
  FROM orders
  WHERE ordered_at >= '2024-01-01'
    AND ordered_at <  '2024-02-01'
),
feb_users AS (  -- 2. February customers
  SELECT DISTINCT user_id
  FROM orders
  WHERE ordered_at >= '2024-02-01'
    AND ordered_at <  '2024-03-01'
)

-- 3. Keep customers present in both months
SELECT
  j.user_id
FROM jan_users j
JOIN feb_users f  -- intersection only
  ON j.user_id = f.user_id;

/*
  Execution order:
  1. jan_users   → extract user_id from January orders
  2. feb_users   → extract user_id from February orders
  3. INNER JOIN  → keep only user_id values present in both lists
  */

The technique from Q5—aggregate the maximum date and JOIN it back to the source table—is reliable, but it can return multiple rows when several orders share the same date.

In modern SQL, including PostgreSQL 8.4+ and MySQL 8.0+, combining the window function ROW_NUMBER() with a CTE has become the best practice—and de facto standard—for latest-row queries.

-- ROW_NUMBER() OVER (PARTITION BY grouping_key ORDER BY sort_order)
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC)
-- → Within each user_id, number rows 1, 2, 3... from newest to oldest ordered_at

Use ROW_NUMBER() and a CTE to retrieve each user's latest order from the orders table.

1. CTE ranked_orders: Use ROW_NUMBER() to assign a row number (rn) within each user_id, ordered by ordered_at in descending order (newest first).
2. Main query: Keep only rn = 1 (the latest row) from the CTE and return user_id, order_id, amount, ordered_at.

-- 1. Number each user's orders in a CTE
WITH ranked_orders AS (
  SELECT
    user_id,
    order_id,
    amount,
    ordered_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn  -- newest first
  FROM orders
)

-- 2. Keep each user's latest row
SELECT
  user_id,
  order_id,
  amount,
  ordered_at
FROM ranked_orders
WHERE rn = 1  -- latest row
ORDER BY user_id;

/*
  Execution order:
  1. ranked_orders (CTE)  → assign rank (rn) within each user_id
  2. main query WHERE rn=1    → keep only each user's latest row
  */

Analytics workloads frequently compare an individual row with an aggregate value, such as an average, for the group to which that row belongs—for example, selecting products priced above their category average.

Historically, this was written with a correlated subquery—a SELECT executed from within the SELECT or WHERE clause—but that style is difficult to read and can perform poorly. The modern mainstream approach is to calculate category averages in a CTE, then JOIN and compare them in the main query.

WITH group_stats AS (
  SELECT group_id, AVG(value) AS avg_value
  FROM items
  GROUP BY group_id
)
SELECT i.id, i.value, g.avg_value
FROM items i
JOIN group_stats g ON i.group_id = g.group_id
WHERE i.value >= g.avg_value;

From the products table, select products whose price is greater than or equal to the average price of their own category.

1. CTE category_avgs: Calculate the average price (avg_price) for each category_id.
2. Main query: Join products with category_avgs and filter on price >= avg_price.
3. Return the product id, name, price, and avg_price.

-- 1. Average price per category
WITH category_avgs AS (
  SELECT
    category_id,
    AVG(price) AS avg_price
  FROM products
  GROUP BY category_id
)

-- 2. Join products to their category average
SELECT
  p.id,
  p.name,
  p.price,
  c.avg_price
FROM products p
JOIN category_avgs c
  ON p.category_id = c.category_id
WHERE p.price >= c.avg_price  -- at or above average
ORDER BY p.id;

/*
  Execution order:
  1. category_avgs  → aggregate the average price for each category
  2. JOIN           → join each product to its category average
  3. WHERE          → keep only products at or above the average
  */

One of the most powerful CTE features is WITH RECURSIVE, a recursive CTE. It repeatedly uses one result to perform the next search until no more data is found.

It retrieves potentially unbounded hierarchies in a single SQL statement, including e-commerce category trees (parent → child → grandchild), threaded comment replies, and organization charts.

WITH RECURSIVE tree AS (
  SELECT id, parent_id              -- anchor term (starting point)
  FROM nodes
  WHERE id = 1

  UNION ALL

  SELECT n.id, n.parent_id         -- recursive term (next level)
  FROM nodes n
  JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

The categories table represents parent-child relationships through parent_id. A NULL parent_id marks a top-level category.

Use a recursive CTE starting at id = 1 (Electronics) to retrieve all descendant categories below it, including children and grandchildren.

-- RECURSIVE lets category_tree reference itself
WITH RECURSIVE category_tree AS (

  -- Step 1: anchor row
  -- Start at id = 1 (Electronics)
  SELECT id, name, parent_id
  FROM categories
  WHERE id = 1

  UNION ALL  -- append each iteration

  -- Step 2: recursive term
  -- Find children of the previous iteration
  SELECT
    c.id, c.name, c.parent_id
  FROM categories c
  JOIN category_tree ct     -- self-reference
    ON c.parent_id = ct.id  -- match each child to its parent
)

-- Step 3: final output
SELECT * FROM category_tree;

/*
  Execution simulation:
  1. Anchor: WHERE id = 1 selects Electronics (id=1).
  2. Iteration 1: using Electronics (id=1) as parent ct selects PC (id=2), whose parent_id is 1.
  3. Iteration 2: using PC (id=2) as parent ct selects Laptop (id=3) and Desktop (id=4), whose parent_id is 2.
  4. Iteration 3: using Laptop and Desktop as parents finds no rows, so recursion stops.
  5. The selected rows 1, 2, 3, and 4 are combined and returned. Books (id=5) is excluded because it belongs to another tree.
*/