Postgres Practice 9/12

Chaining CTEs

One CTE names one step. Real questions often take two or three: "total sales per month, then find the best month, then show me who sold what in that month." Each step consumes the previous one - a pipeline.

A single WITH clause can hold several CTEs, separated by commas, and each one can query the ones defined before it:

WITH monthly AS (
  SELECT month, SUM(amount) AS total FROM sales GROUP BY month
),
best AS (
  SELECT month FROM monthly ORDER BY total DESC LIMIT 1
)
SELECT ...

best reads from monthly as if it were a table - the second step consumes the first. The final query after the WITH block can then use either one. Note the comma between CTEs, and that WITH is written only once - two classic syntax stumbles when chaining for the first time.

Same sales table as the last lesson: rep, region, month, amount.

Your task: using two CTEs - monthly (month, total) and best (the single month with the highest total) - list the rep and amount of every sale that happened in the best month. Order by rep.

You'll practice:

Defining two CTEs in one WITH clause, comma-separated
Having the second CTE query the first

-- Step 1: total per month. Step 2: the best month. Step 3: that month's sales.
WITH monthly AS (
  SELECT month, SUM(amount) AS total
  FROM sales
  GROUP BY month
)
SELECT month, total FROM monthly ORDER BY month;