Order Statuses per Customer — STRING_AGG and ARRAY

The problem

Brightlane's CRM team wants a one-row summary per customer showing every status that customer has experienced across their order history.

Write a query to return every customer's ID and a comma-separated list of their order statuses, with the statuses arranged in alphabetical order within each list.

Assumptions:

The orders table has one row per order with a customer_id and a status.
Each customer_id with at least one order should appear once in the result.
For each customer, the status list contains every status value across that customer's orders (one entry per order, no de-duplication), separated by ', ' and arranged alphabetically.

Output:

One row per customer with at least one order, with columns customer_id and order_statuses.

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,
  STRING_AGG(
    status,
    ', '
    ORDER BY
      status
  ) AS order_statuses
FROM
  orders
GROUP BY
  customer_id

The shape

STRING_AGG(status, ', ' ORDER BY status) collapses every order belonging to one customer into a single text value, with the status of each order written out in alphabetical sequence. The CRM team gets one row per customer carrying the full history.

Clause by clause

SELECT customer_id, STRING_AGG(status, ', ' ORDER BY status) AS order_statuses returns the customer's ID alongside their concatenated status list. The first argument names the column to collect; the second is the literal delimiter ', ' placed between adjacent values. The ORDER BY status inside the aggregate fixes the order the values join in, which is what gives 'delivered, delivered, shipped' instead of an unpredictable arrangement.
FROM orders reads the order rows. Every order on file is in scope; the prompt asks for the full history per customer.
GROUP BY customer_id partitions the rows by customer so the aggregate runs once per customer instead of once across the whole table. One output row comes out for each distinct customer_id, which matches the "one row per customer" output spec.

Why this and not `SUM` or `COUNT`

Standard aggregates like SUM and COUNT collapse the rows in each group to a single number; the contributing values are gone. STRING_AGG is the aggregate that keeps them. Same grouping mechanics, different output shape: a delimited string carrying every value the group contributed.

You practiced STRING_AGG(column, separator ORDER BY column) — collect grouped values into a delimited text string with a deterministic order inside the aggregate.

Return every customer's ID and a comma-separated list of their order statuses, with the statuses arranged in alphabetical order within each list

The shape

Clause by clause

Why this and not `SUM` or `COUNT`

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 SUM or COUNT

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

Why this and not `SUM` or `COUNT`