Customer Spend Summary — CREATE TABLE AS in SQL

The problem

Brightlane's revenue pipeline materializes per-customer spending totals alongside each customer's order count into a temp table, restricted to customers who have placed more than one order. The temp table feeds multiple downstream reports.

Write a query to return each qualifying customer's ID, order count, and total spend.

Assumptions:

The orders table has one row per order with a customer_id and a total_amount.
A customer's order count is the number of orders linked to that customer_id. A customer's total spend is the combined total_amount across those orders.
Only customers whose order count is greater than 1 should appear.

Output:

One row per qualifying customer, with columns customer_id, order_count, and total_spent.

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
  customer_stats AS (
    SELECT
      customer_id,
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_spent
    FROM
      orders
    GROUP BY
      customer_id
  )
SELECT
  customer_id,
  order_count,
  total_spent
FROM
  customer_stats
WHERE
  order_count > 1

The shape

A CTE computes the per-customer count and combined spend; the outer query keeps only the customers whose count exceeds 1. The CTE exists for the same reason the derived-table version exists: the threshold is a condition on order_count, a column that does not exist until GROUP BY has run. Naming the aggregation result with WITH ... AS makes the two stages read in the natural order, top to bottom.

Clause by clause

WITH customer_stats AS (SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) defines a named intermediate table that holds the two metrics per customer. The CTE body is a complete SELECT with a GROUP BY customer_id; once it finishes, customer_stats is available to the rest of the statement.
SELECT customer_id, order_count, total_spent FROM customer_stats reads the CTE and returns all three columns.
WHERE order_count > 1 filters the CTE result to customers with more than one order. The aggregation has already happened inside the CTE, so order_count is a real value on each row of customer_stats and the comparison is straightforward.

Why this and not a derived table

A CTE and a derived table compute the same result on this problem. The CTE wins on readability for multi-step pipelines: the aggregation gets its own named block at the top, the threshold reads as a simple WHERE on a named table at the bottom, and the two ideas are visually separated. For the materialization step itself, the choice of CTE versus derived table inside the CTAS body is purely stylistic. The temp table the pipeline ultimately writes is identical either way.

You practiced the per-customer aggregation pattern with two metrics — count and combined spend — plus a threshold on one of them, all in a shape that's ready to materialize as a temp table.

Return each qualifying customer's ID, order count, and total spend

The shape

Clause by clause

Why this and not a derived table

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