High Volume Statuses Materialized — CREATE TABLE AS in SQL

The problem

Brightlane's fulfillment analysis pipeline materializes high-volume statuses — those with more than 10 orders on record — into a temp table for downstream operations reporting.

Write a query to return the status and order count for each high-volume status.

Assumptions:

The orders table has one row per order with a status.
A status's order count is the number of orders carrying that status.
Only statuses with an order count greater than 10 should appear.

Output:

One row per qualifying 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
  ),
  high_volume AS (
    SELECT
      status,
      order_count
    FROM
      status_counts
    WHERE
      order_count > 10
  )
SELECT
  status,
  order_count
FROM
  high_volume

The shape

A two-stage chained CTE: the first stage groups orders by status and counts them, the second stage filters that result to statuses whose count exceeds 10. Two separate WITH blocks make each stage independently named, which is the readability win when a pipeline plans to materialize each intermediate result on its way to the final temp table.

Clause by clause

WITH status_counts AS (SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status) is the first CTE. It groups every order by status and counts the orders in each group, producing one row per distinct status.
high_volume AS (SELECT status, order_count FROM status_counts WHERE order_count > 10) is the second CTE, which reads the first one and keeps only the rows whose count exceeds 10. The filter compares against order_count, which the first CTE made available as a real column.
SELECT status, order_count FROM high_volume reads the filtered result and returns both columns. On this data the four high-volume statuses survive: delivered at 161, shipped at 17, cancelled and pending at 11 each.

Why chain two CTEs and not collapse into one

A single CTE wrapping a derived-table filter would compute the same result. The chained form pays off when the pipeline plans to materialize both stages: status_counts is itself a reusable intermediate, and other downstream queries in the same session might want the full count list before the high-volume filter is applied. Naming each stage separately means each one can become a temp table of its own. Collapsing the two into a single expression hides that boundary.

You practiced a two-stage aggregation: count records per category, then keep only categories whose count exceeds a threshold — a compact restricted-aggregate shape ready to populate a temp table.

Return the status and order count for each high-volume status

The shape

Clause by clause

Why chain two CTEs and not collapse into one

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