Order Amounts Array per Customer — STRING_AGG and ARRAY

The problem

Brightlane's finance team wants every customer's purchase history as a structured collection of amounts.

Write a query to return every customer ID and an array of all their order amounts, with the amounts sorted from smallest to largest within each array.

Assumptions:

The orders table has one row per order with a customer_id and a total_amount.
Each customer_id with at least one order should appear once.
For each customer, the array contains every total_amount value across that customer's orders (one element per order, no de-duplication), arranged from smallest to largest within the array.

Output:

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

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,
  ARRAY_AGG(
    total_amount
    ORDER BY
      total_amount
  ) AS order_amounts
FROM
  orders
GROUP BY
  customer_id

The shape

ARRAY_AGG(total_amount ORDER BY total_amount) collects every order amount belonging to one customer into a numeric array, with the elements sorted ascending inside the array. The finance team gets one row per customer and a typed array of amounts that downstream code can iterate, index, or sum without re-parsing.

Clause by clause

SELECT customer_id, ARRAY_AGG(total_amount ORDER BY total_amount) AS order_amounts returns the customer's ID and the array of their order amounts. ARRAY_AGG collects the input values into a PostgreSQL array; the column type follows the input type, so an array of total_amount values comes back as a numeric array. The ORDER BY total_amount inside the aggregate fixes the element sequence, which is what produces [129.98, 249.00, 799.00, 1099.00, 1999.00] for customer 1 rather than the table's physical order.
FROM orders reads the order rows. Every order on file contributes.
GROUP BY customer_id partitions the rows by customer so the aggregate runs once per customer. One output row per distinct customer_id, matching the per-customer history the output spec calls for.

Why this and not `STRING_AGG(total_amount::text, ', ' ORDER BY total_amount)`

A text list would render cleanly in a report, but the prompt is about a structured collection, not a display value. ARRAY_AGG keeps the amounts numeric, so consuming code can index into a single amount, run unnest() to expand them back into rows, or pass the array to a function. A STRING_AGG result is just text; once the amounts are concatenated, the numbers are gone.

You practiced ARRAY_AGG(column ORDER BY column) over a numeric column — every contributing value is preserved as a typed numeric array element, ready for downstream array operations.

Return every customer ID and an array of all their order amounts, with the amounts sorted from smallest to largest within each array

The shape

Clause by clause

Why this and not `STRING_AGG(total_amount::text, ', ' ORDER BY total_amount)`

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 STRING_AGG(total_amount::text, ', ' ORDER BY total_amount)

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

Why this and not `STRING_AGG(total_amount::text, ', ' ORDER BY total_amount)`