Brightlane's warehouse team wants the average quantity per order line item — precise enough to use in a capacity model, not rounded to a whole number.
Write a query to return the exact average in a single column named avg_quantity.
Assumptions:
- The
order_itemstable contains one row per product per order. quantityis stored as an integer column.- The result must preserve the fractional portion — a value like
2.4737...is the answer, not2.
Output:
- A single row with one column,
avg_quantity, expressed as a decimal value.
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
AVG(quantity) AS avg_quantity
FROM
order_itemsThe shape
AVG(quantity) returns 1.05 — a decimal with a fractional part — even though quantity is stored as an integer column. PostgreSQL's AVG automatically widens integer inputs to numeric so the fractional portion of the mean survives the operation, which is exactly what the warehouse capacity model needs.
Clause by clause
FROM order_itemsis the source set: one row per (order, product) line, withquantityas an integer count of units on each line.AVG(quantity)walks the column, divides the sum by the count, and returns the mean. The load-bearing behavior here is the type promotion: PostgreSQL knows that the average of integers is rarely a whole number, so it returns anumericrather than an integer. The fractional part survives the calculation by default.AS avg_quantitylabels the result. The warehouse team can drop the column straight into a capacity model without renaming it.
Why this and not SUM(quantity) / COUNT(*)
AVG(quantity) and SUM(quantity) / COUNT(*) are mathematically the same operation — total divided by count. But they have very different behavior in PostgreSQL when the input is an integer column.
AVG returns numeric automatically. SUM(quantity) / COUNT(*) runs as integer-divided-by-integer, because SUM over an integer column and COUNT(*) both return integers. PostgreSQL's integer division drops the fractional part of the result entirely. For this data set, the division would return 1 instead of 1.05. The capacity model would be working with the wrong number, and there is no error to flag the loss.
To get the same behavior as AVG from a manual division, the cast has to be explicit — turning at least one operand into numeric before the division happens. AVG does that automatically; the manual division leaves it to the author.
The trap
The trap is reaching for the textbook formula (SUM / COUNT) instead of the built-in aggregate, and getting integer truncation as a silent side effect. The numbers look reasonable — 1 is plausible as an average quantity per line — but they're wrong by the fractional portion. For a capacity model that scales by tens of thousands of line items, a lost 0.05 per line compounds into a meaningful underestimate of total throughput.
The rule: when averaging an integer column, prefer AVG to a hand-rolled division. The function exists precisely to make the type widening automatic. Reach for it.
You practiced relying on AVG's automatic type-widening. Even when the input column is integer, AVG returns numeric to preserve the fractional part — unlike division (SUM(quantity) / COUNT(*)), which would truncate without an explicit cast.
