Order Counts by Status — CTEs in SQL

The problem

Brightlane's operations team maintains a daily pipeline log showing how many orders sit in each stage.

Write a query to return the count of orders in each status.

Assumptions:

The orders table has one row per order with a status.
Each status value present in orders should appear once in the report.

Output:

One row per status, with columns status and order_count.

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_counts AS (
    SELECT
      status,
      COUNT(*) AS order_count
    FROM
      orders
    GROUP BY
      status
  )
SELECT
  status,
  order_count
FROM
  status_counts

The shape

A WITH layer named status_counts computes the per-status COUNT(*) once, and the main query reads from it by name. The layer is the named subquery; the main query treats the name like a table.

Clause by clause

The WITH clause defines the named layer:

WITH status_counts AS (
  SELECT status, COUNT(*) AS order_count
  FROM orders
  GROUP BY status
)

Inside the parentheses, SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status produces one row per distinct status with the count attached. That whole result set is what the name status_counts refers to.

SELECT status, order_count FROM status_counts is the main query. It reads from the named layer by name and returns its two columns straight through. The row for 'delivered' carries order_count = 161; 'shipped' carries 17; 'cancelled' and 'pending' each carry 11.

Why this and not a derived table in `FROM`

A derived table would compute the same per-status counts inside the FROM clause of the main query, no WITH involved. Both shapes return the same rows. The WITH version moves the aggregation above the main query and gives it a name, which means the main query reads top to bottom in the order the work happens. A derived table buries the aggregation inside the FROM clause of the same statement that consumes it. For a single-use named layer like this, the difference is organizational, not structural; WITH is the more readable spelling.

You practiced lifting a per-category breakdown into a named WITH layer that the main query reads from like a table.

Return the count of orders in each status

The shape

Clause by clause

Why this and not a derived table in `FROM`

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

The shape

Clause by clause

Why this and not a derived table in FROM

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

Why this and not a derived table in `FROM`