Customer Order Totals in Stages — Multi-CTE Queries in SQL

The problem

Scenario: Brightlane's CRM team is building a customer overview report that lists each customer alongside how many orders they have placed and how much they have spent.

Task: Write a query to return each customer's id, name, number of orders placed, and total amount spent across all their orders.

Assumptions:

The result covers only customers who have placed at least one order.

Output:

One row per customer who has placed at least one order.
Columns in this order: customer_id, customer_name, order_count, total_spent.
Sorted by total_spent descending.

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
  orders_with_customer AS (
    SELECT
      o.customer_id,
      c.name AS customer_name,
      o.total_amount
    FROM
      orders o
      JOIN customers c ON o.customer_id = c.id
  ),
  customer_totals AS (
    SELECT
      customer_id,
      customer_name,
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_spent
    FROM
      orders_with_customer
    GROUP BY
      customer_id,
      customer_name
  )
SELECT
  customer_id,
  customer_name,
  order_count,
  total_spent
FROM
  customer_totals
ORDER BY
  total_spent DESC

The shape

Two CTEs, one job each. The first pairs every order with its customer's name, and the second collapses those rows into one summary line per customer. Splitting the join and the aggregation into separate named layers means either step can be inspected on its own, and the join is settled before any counting starts.

Clause by clause

WITH orders_with_customer AS (
    SELECT o.customer_id, c.name AS customer_name, o.total_amount
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
)

The join layer reads orders and attaches each row's customer name from customers. Only the three columns the next layer needs are carried forward.

customer_totals AS (
    SELECT customer_id, customer_name, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
    FROM orders_with_customer
    GROUP BY customer_id, customer_name
)

GROUP BY customer_id, customer_name produces one row per customer. COUNT(*) is the order count and SUM(total_amount) is the lifetime spend. Both grouping columns are listed because the name has to ride along with the ID into the output.

SELECT customer_id, customer_name, order_count, total_spent FROM customer_totals ORDER BY total_spent DESC reads the summary unchanged and sorts the biggest spenders first. Xander Wright leads at 5096, Jack Miller follows at 4995.

You practiced decomposing the work into two CTEs — one that brings customers together with orders, another that totals each customer's spend — so each step does a single transformation.

Return each customer's `id`, name, number of `orders` placed, and total amount spent across all their `orders`

The shape

Clause by clause

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