You can define multiple CTEs by separating them with commas. This is especially useful when you need to preprocess several tables—by filtering or aggregating them—before a JOIN.

WITH cte_A AS (
  SELECT ... -- first CTE
),
cte_B AS (
  SELECT ... -- second CTE (connect with a comma; do not repeat WITH)
)
SELECT * FROM cte_A JOIN cte_B ON ... ;

Create a CTE named premium_users that selects users with rank = 'premium' from users.

Next, create a CTE named high_orders that selects orders with amount >= 5000 from orders.

Finally, INNER JOIN the two CTEs on user_id in the main query and return the user name (name) and order amount (amount).

-- First CTE: select premium users
WITH premium_users AS (
  SELECT user_id, name
  FROM users
  WHERE rank = 'premium'
),                        -- connect CTEs with a comma (essential)
high_orders AS (          -- Second CTE: select high-value orders of 5000 or more
  SELECT user_id, amount
  FROM orders
  WHERE amount >= 5000
)

-- Main query: join the two CTEs
SELECT
  p.name,
  h.amount
FROM premium_users p
JOIN high_orders h
  ON p.user_id = h.user_id;

/*
  Execution order:
  1. Evaluate premium_users → hold it temporarily in memory
  2. Evaluate high_orders   → hold it temporarily in memory
  3. JOIN in the main query → match on user_id
  */

One of the most useful CTE patterns is joining results aggregated with GROUP BY to master data such as user information.

Forcing aggregation (GROUP BY) and joining (JOIN) into one SELECT can make the code complicated. Extracting the aggregation into a CTE keeps the statement clean.

Create a CTE named user_sales that calculates each user’s total order amount (total_amount) from orders.

Then JOIN users and user_sales in the main query and return the user name (name) and total order amount.

Sort the output by total order amount in descending order, highest first.

-- 1. CTE: aggregate total order amount per user from orders
WITH user_sales AS (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
)

-- 2. Main query: join the aggregate CTE to users to retrieve names
SELECT
  u.name,
  s.total_amount
FROM users u
JOIN user_sales s
  ON u.user_id = s.user_id
ORDER BY s.total_amount DESC;

/*
  Execution order:
  1. Evaluate user_sales         → aggregate orders by user_id
  2. JOIN users and user_sales   → match on user_id
  3. ORDER BY                    → sort total amount descending
  */

When multiple CTEs are defined in a comma-separated chain, a later CTE can reference a CTE defined before it.

WITH cte_1 AS (
  SELECT ... -- Step A (for example, remove unnecessary data)
),
cte_2 AS (
  SELECT ... FROM cte_1  -- Step B (further aggregate Step A’s result)
)
SELECT * FROM cte_2;     -- final output

Chaining processing in this way lets you read and write SQL like a data pipeline flowing from top to bottom.

Implement the following steps as a chain of CTEs over the orders table.

1. CTE valid_orders: select only orders whose status is not 'cancelled'.
2. CTE user_totals: aggregate each user’s total amount (total) from valid_orders.
3. Main query: from user_totals, retrieve user_id and total for users whose total is at least 10000.

-- 1. First CTE: exclude canceled orders
WITH valid_orders AS (
  SELECT user_id, amount
  FROM orders
  WHERE status != 'cancelled'
),
user_totals AS (               -- 2. Second CTE: aggregate by referencing valid_orders
  SELECT
    user_id,
    SUM(amount) AS total
  FROM valid_orders            -- reference the previously defined CTE
  GROUP BY user_id
)

-- 3. Main query: filter by referencing the second CTE, user_totals
SELECT
  user_id,
  total
FROM user_totals
WHERE total >= 10000;  -- only users whose total is at least 10000

/*
  Execution order:
  1. Exclude cancelled from orders → build valid_orders
  2. Group by user_id              → build user_totals
  3. Filter on the total threshold → keep qualifying rows
  */

A very common production web-application task—for example, on an account page—is retrieving each user’s latest record, such as their newest order or login history entry.

Several techniques can solve this problem. One of the most general and understandable is to calculate each user’s latest timestamp with MAX in a CTE, then JOIN that result to the source table to identify the corresponding row.

Retrieve each user’s latest order—the row with the greatest ordered_at—from the orders table.

First, in a CTE named latest_dates, aggregate the greatest ordered_at for each user and name it max_date.
Then JOIN the original orders table to the CTE on both user_id and ordered_at = max_date. Return order_id, user_id, amount, and ordered_at for each latest order.

-- 1. CTE: identify each user’s newest order date
WITH latest_dates AS (
  SELECT
    user_id,
    MAX(ordered_at) AS max_date  -- use MAX to obtain the latest date
  FROM orders
  GROUP BY user_id
)

-- 2. Main query: join the source table to the CTE and keep only the latest rows
SELECT
  o.user_id,
  o.order_id,
  o.amount,
  o.ordered_at
FROM orders o
JOIN latest_dates ld
  ON o.user_id = ld.user_id       -- composite JOIN: same user_id and order date equal to max_date
  AND o.ordered_at = ld.max_date
ORDER BY o.user_id;

/*
  Execution order:
  1. Build latest_dates
  2. Join orders (o) to latest_dates (ld)
  */