Postgres Practice 8/12

CTEs: naming a step with WITH

"Which sales reps beat the average?" sounds like one question, but it's two stacked queries: first total up each rep's sales, then compare those totals to their own average. Written with subqueries, the same GROUP BY ends up pasted in twice, nested inside itself - correct, and nearly unreadable a week later.

A CTE (Common Table Expression) lets you name the first step and then treat it like a table:

WITH rep_totals AS (
  SELECT rep, SUM(amount) AS total
  FROM sales
  GROUP BY rep
)
SELECT ... FROM rep_totals ...

Everything after the WITH block can query rep_totals as if it were a real table - including using it twice: once for the rows, once inside an aggregate to get the average. One definition, two uses, zero duplication. That's the whole pitch of CTEs: name your steps, and stacked questions read top-to-bottom like a recipe.

You have a sales table: rep, region, month, amount - two months of sales for four reps.

Your task: using a CTE named rep_totals (rep, total), list the rep and total of every rep whose total is above the average of all reps' totals. Order by rep.

You'll practice:

Writing WITH name AS (...) and querying it
Referencing the same CTE twice - as rows and inside an aggregate

-- Which reps' totals beat the average total?
-- Step 1: total per rep. Step 2: compare to the average of those totals.
WITH rep_totals AS (
  SELECT rep, SUM(amount) AS total
  FROM sales
  GROUP BY rep
)
SELECT rep, total FROM rep_totals ORDER BY rep;