Delivered Count and Revenue per Customer

The problem

Brightlane's sales team is building a customer performance dashboard.

Write a query to return every customer ID, their total order count, the number of delivered orders, and the total revenue from delivered orders only.

Assumptions:

The orders table has one row per order with a customer_id, a status, and a total_amount.
Each customer_id with at least one order should appear once.
For each customer, the total count covers every order linked to that customer_id. The delivered count covers only orders with status = 'delivered', and the delivered revenue is the combined total_amount across those delivered orders.

Output:

One row per customer with at least one order, with columns customer_id, total_orders, delivered_count, and delivered_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

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (
    WHERE
      status = 'delivered'
  ) AS delivered_count,
  SUM(total_amount) FILTER (
    WHERE
      status = 'delivered'
  ) AS delivered_revenue
FROM
  orders
GROUP BY
  customer_id

The shape

The same FILTER (WHERE status = 'delivered') condition attaches to two different aggregates inside one SELECT list. COUNT and SUM each restrict to the same delivered subset, but compute different statistics over it — and they coexist with the unfiltered COUNT(*) covering every order, all in one GROUP BY pass.

Clause by clause

SELECT customer_id, COUNT(*) AS total_orders, COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_count, SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered_revenue returns each customer's total order count, their count of delivered orders, and the combined total_amount across those delivered orders. The first COUNT(*) runs over every row in the partition; the next two aggregates run only over the partition's delivered rows. A customer with zero delivered orders gets a delivered count of 0 and a delivered revenue of NULL — because SUM over zero rows is NULL, the same way SUM is NULL for any empty input.
FROM orders reads the order records.
GROUP BY customer_id partitions the rows per customer. The three aggregates evaluate inside each customer's partition independently.

Why repeat the same `FILTER` on two aggregates instead of pre-filtering with `WHERE`

A WHERE status = 'delivered' would drop non-delivered rows before aggregation, which would also drop them from the COUNT(*) covering every order. The dashboard needs the total across every status alongside the delivered subset, in the same row. FILTER is the only construct in scope that restricts individual aggregates without restricting the whole partition. The duplication on the FILTER clauses is the price of keeping the unfiltered total honest.

You practiced FILTER on SUM as well as COUNT — the same condition restricts two different aggregates to the same delivered subset within each partition.

Return every customer ID, their total order count, the number of delivered orders, and the total revenue from delivered orders only

The shape

Clause by clause

Why repeat the same `FILTER` on two aggregates instead of pre-filtering with `WHERE`

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

The shape

Clause by clause

Why repeat the same FILTER on two aggregates instead of pre-filtering with WHERE

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

Why repeat the same `FILTER` on two aggregates instead of pre-filtering with `WHERE`