Product IDs per Order — STRING_AGG and ARRAY

The problem

Brightlane's order processing team needs a line-item summary per order showing the product IDs that were included as a comma-separated text string.

Write a query to return every order ID and its product IDs as a comma-separated string, with the IDs arranged in ascending numeric order.

Assumptions:

The order_items table has one row per line item with an order_id and a product_id.
Each order_id with at least one line item should appear once.
For each order, the product-IDs list contains every product_id value of items in that order (one entry per line item, no de-duplication), with the IDs sorted numerically (not lexically) before being assembled with ', ' between adjacent values.

Output:

One row per order with at least one line item, with columns order_id and product_ids.

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
  order_id,
  STRING_AGG(
    product_id::TEXT,
    ', '
    ORDER BY
      product_id
  ) AS product_ids
FROM
  order_items
GROUP BY
  order_id

The shape

STRING_AGG(product_id::text, ', ' ORDER BY product_id) joins each order's product IDs into a single text value, with the IDs sorted by their numeric value before they are concatenated. The ::text cast handles the type requirement of STRING_AGG without changing the sort order, because the ORDER BY is evaluated against the original numeric column.

Clause by clause

SELECT order_id, STRING_AGG(product_id::text, ', ' ORDER BY product_id) AS product_ids returns the order ID alongside the concatenated product-ID list. STRING_AGG requires its first argument to be text, which is why product_id::text converts the integer column into text values the aggregate can join. The ORDER BY product_id references the original integer column, so the sequence is 14, 22, 32 — numeric order — not the lexical order a text sort would give (14, 22, 32 happens to match here, but on IDs like 2, 11, 100 a text sort would produce 100, 11, 2 instead). The ', ' literal sits between adjacent values.
FROM order_items reads the line-item rows. Every line item contributes to its order's list.
GROUP BY order_id partitions the rows by order so the aggregate runs once per order. One output row per distinct order_id.

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

Both compile, but they produce different results once IDs cross digit-length boundaries. Sorting by the cast text orders the values lexically: '100' sorts before '11' because '0' comes before '1' character by character. Sorting by the underlying product_id orders them numerically, which is what the prompt asks for. Cast for concatenation, sort against the typed source.

You practiced STRING_AGG(column::text, separator ORDER BY column) — cast a numeric column to text for concatenation while keeping the numeric ordering intact.

Return every order ID and its product IDs as a comma-separated string, with the IDs arranged in ascending numeric order

The shape

Clause by clause

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

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

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

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