Statuses with High Revenue — CTEs in SQL

The problem

Brightlane's finance team is reviewing pipeline health by order status.

Write a query to return the status, order count, and total revenue for every status whose combined order revenue exceeds $5,000.

Assumptions:

The orders table has one row per order with a status and a total_amount.
A status's combined order revenue is the total of total_amount across every order in that status.
Only statuses whose combined revenue exceeds $5,000 should appear.

Output:

One row per qualifying status, with columns status, order_count, and total_revenue.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH
  status_revenue AS (
    SELECT
      status,
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_revenue
    FROM
      orders
    GROUP BY
      status
  )
SELECT
  status,
  order_count,
  total_revenue
FROM
  status_revenue
WHERE
  total_revenue > 5000

The shape

One WITH layer computes both per-status figures at once. GROUP BY status partitions the rows, and COUNT(*) and SUM(total_amount) produce two aggregates side by side. The main query reads all three columns and applies the threshold against total_revenue.

Clause by clause

The WITH clause defines status_revenue:

WITH status_revenue AS (
  SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS total_revenue
  FROM orders
  GROUP BY status
)

A single grouping pass produces both aggregates per status. The layer ends up with one row per status carrying its count and its revenue total: 'delivered' shows 161 orders and $104,331.81; 'shipped' shows 17 orders and $10,954.98; the other statuses follow the same shape.

SELECT status, order_count, total_revenue FROM status_revenue WHERE total_revenue > 5000 is the main query. It reads the layer and keeps only the rows where revenue exceeds 5000. Every status on this data clears the threshold, since the smallest total_revenue is 5290.98 on 'pending'.

Why one layer with both aggregates, not two

A single GROUP BY status scan can carry as many aggregate expressions as you list in the SELECT. Computing COUNT(*) and SUM(total_amount) in the same layer reads the table once and produces both columns. Splitting them into two layers would force two separate aggregations over the same row partitions, which is the same work duplicated. Two aggregates over the same grouping go in the same layer.

The trap

The threshold runs against total_revenue, not order_count. The two aggregates land side by side in the layer's output, which makes it easy to filter on the wrong one by reflex. WHERE order_count > 5000 would silently return a different population, since order_count is a count of orders and total_revenue is a sum of dollars. Naming each aggregate clearly with AS is what makes the filter readable; the names in the main query's WHERE have to match the intent of the threshold.

You practiced computing two per-category figures in one WITH layer and applying a threshold against one of them in the main query.

Return the status, order count, and total revenue for every status whose combined order revenue exceeds `$5,000`

The shape

Clause by clause

Why one layer with both aggregates, not two

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.