Brightlane's customer service team wants every customer's order history as a sequence of statuses ordered by purchase value within each customer — smallest amount first, largest last. The customer rows themselves should be sorted by ID.
Write a query to return every customer ID and a comma-separated list of order statuses arranged in ascending order of total_amount within that customer's history. Sort the final result by customer_id ascending.
Assumptions:
- Each
customer_idwith at least one order should appear once. - For each customer, the statuses list contains every order's
statusvalue (one entry per order, no de-duplication), arranged by ascendingtotal_amountwithin the list, separated by', '. - The final result is sorted by
customer_idascending.
Output:
- One row per customer with at least one order, with columns
customer_idandstatuses_by_amount. Sorted bycustomer_idascending.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
customer_id,
STRING_AGG(
status,
', '
ORDER BY
total_amount
) AS statuses_by_amount
FROM
orders
GROUP BY
customer_id
ORDER BY
customer_idThe shape
Two independent orderings sit in the same query and do not interfere with each other. STRING_AGG(status, ', ' ORDER BY total_amount) controls the sequence of statuses inside one customer's value: smallest order first, largest last. The query-level ORDER BY customer_id controls which customer row prints before which. Both are required, and each refuses to do the other one's job.
Clause by clause
SELECT customer_id, STRING_AGG(status, ', ' ORDER BY total_amount) AS statuses_by_amountreturns the customer's ID and the per-customer status list. The status values are concatenated with', 'between them; theORDER BY total_amountinside the aggregate orders the contributing rows by purchase value before the join happens, so a customer whose orders are($50, delivered), ($200, cancelled), ($90, shipped)produces'delivered, shipped, cancelled'(sorted by amount, status carried along).FROM ordersreads the order rows. Every order is in scope.GROUP BY customer_idpartitions the rows by customer so the aggregate runs once per customer. One output row per distinctcustomer_id.ORDER BY customer_idsorts the printed result set so customer 1 prints before customer 2. This is a separate clause from the aggregate's internalORDER BY; it acts on the rows produced byGROUP BY, not on the rows feeding the aggregate.
The trap
Conflating the two ORDER BY positions silently produces the wrong answer. Writing ORDER BY total_amount at the query level instead of inside the aggregate would try to sort the printed result by an amount, but total_amount is not a grouping key and is not selected, so the query fails outright. Writing ORDER BY customer_id inside the aggregate would compile, but it would order the contributing rows by their customer_id — which is the same value for every row in the group, so the resulting sequence would be the table's physical order, not the amount order the prompt asks for. The rule is positional: the aggregate's ORDER BY shapes the value, the query's ORDER BY shapes the result set. They are not interchangeable.
You practiced two independent orderings in one query — STRING_AGG(... ORDER BY total_amount) controls the sequence inside each cell; the query-level ORDER BY customer_id controls which row appears first in the result.
