Orders Ranked by Item Count — Debugging Queries in SQL

The problem

Scenario: Brightlane's data engineer suspects some orders carry unusually high item counts, which could explain inflated revenue totals in a downstream report. The diagnostic ranks every order by how many line items it has, so the highest-item-count orders surface first.

Task: Write a query to return each order's id and its item_count — the number of order_items matched to that order.

Assumptions:

An order's item_count is the count of order_items recorded against it.
The result covers only orders with at least one line item on record.

Output:

One row per order with at least one line item.
Columns in this order: order_id, item_count.
Sorted by item_count descending.

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
  o.id AS order_id,
  COUNT(oi.id) AS item_count
FROM
  orders o
  JOIN order_items oi ON oi.order_id = o.id
GROUP BY
  o.id
ORDER BY
  item_count DESC

The shape

Group the joined (order, line-item) rows by order_id and count the line items per order. Sorting that count descending puts the orders with the largest item lists at the top, where the analyst is looking for the few outliers that explain inflated parent-level totals.

Clause by clause

SELECT o.id AS order_id, COUNT(oi.id) AS item_count returns one row per order with its line-item count. COUNT(oi.id) counts the joined line-item rows per group, which is what produces the per-order fanout multiplier.
FROM orders o JOIN order_items oi ON oi.order_id = o.id pairs each order with its line items. The inner join drops orders that have no line items, which is exactly what the prompt asks for (only orders with at least one line item).
GROUP BY o.id collapses the per-line-item rows back into one row per order, so COUNT(oi.id) resolves per order rather than across the whole table.
ORDER BY item_count DESC puts the highest-item-count orders first. The reference shows four orders with three items each at the top, then one with two, then a long tail of singletons. The top of the list is the small set of orders driving any per-order fanout effect.

Why this and not `COUNT(*)`

On this join, COUNT(*) and COUNT(oi.id) return the same numbers — every joined row carries a non-null oi.id. Reaching for COUNT(oi.id) is more deliberate: the count names the thing being counted (line items), which makes the query read as a per-parent child-count diagnostic rather than a generic row count.

You practiced ranking parents by their child-record count — surfacing the parents whose fanout multiplier is largest, the typical entry point for diagnosing inflated parent-level totals.

Return each order's `id` and its `item_count` — the number of `order_items` matched to that order

The shape

Clause by clause

Why this and not `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 COUNT(*)

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

Why this and not `COUNT(*)`