Brightlane's inventory team is analysing how many unique stock-value levels exist across the product catalogue. Stock value for a product is its unit price multiplied by stock_qty (units currently in stock).
Write a query to return each unique stock-value level, with no duplicates.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - Two different products can produce the same stock value (e.g., 100 units at $5 and 50 units at $10 both produce $500).
Output:
- One row per unique stock-value level, with a single column
stock_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 DISTINCT
price * stock_qty AS stock_value
FROM
productsThe shape
DISTINCT runs over whatever the SELECT list produces, not over the columns the table stores. The multiplication evaluates first — price * stock_qty for every product — and then the deduplication runs across that computed column. Two products with different price and stock_qty that happen to multiply to the same stock value collapse into one row in the result, because the values being compared are the products of the expression, not the inputs.
Clause by clause
SELECT DISTINCT price * stock_qty AS stock_valuedoes three things in source order. First, for every row, it evaluatesprice * stock_qty— that's the expression engine running over the row's input columns. Second,DISTINCTruns across the set of computed values, collapsing duplicates. Third,AS stock_valuelabels the output column so the result reads as a domain quantity instead of a multiplication expression.FROM productsis the row source — every product in the catalogue contributes its(price, stock_qty)pair, the multiplication produces a stock value, and the deduplication runs across the full set of stock values.
Why this and not SELECT price, stock_qty FROM products then dedupe
Deduplicating on (price, stock_qty) and deduplicating on price * stock_qty answer different questions. The first asks "how many distinct price/quantity combinations exist?" — 100 units at $5 and 50 units at $10 are two distinct combinations, so both rows survive. The second asks "how many distinct stock-value levels exist?" — 100 × 5 and 50 × 10 both produce $500, so the two rows collapse to one.
The inventory team is asking the second question. They care about how many price-points the stock value lands at, not how many product configurations produce those price-points. Putting the expression inside DISTINCT's scope is the difference between counting configurations and counting outcomes.
The trap
The order of operations is the easy thing to lose track of. DISTINCT doesn't deduplicate the inputs and then evaluate; it evaluates the expression first, then deduplicates the results. The row count reflects the cardinality of the output, not the inputs — which is usually smaller. Any time DISTINCT is paired with a computed column, the rule is: expression first, deduplication second.
You practiced applying DISTINCT to a computed expression rather than a stored column. DISTINCT operates on whatever the SELECT list produces — so deduplication happens after the multiplication, on the result, not on either input column.
