Level 7 · Bestsellers that rarely get discounted

HAVING filters groups the way WHERE filters rows. This ticket needs both: WHERE to clean the input rows, HAVING with two aggregate conditions to pick the products.

WHERE first, HAVING after

SELECT product_id, COUNT(*) AS lines, ROUND(AVG(discount_pct), 2) AS avg_discount_pct
FROM order_items
WHERE quantity > 0
GROUP BY product_id
HAVING COUNT(*) >= 500;

The query runs in this order: FROM, WHERE (drop the 24 lines with a zero or negative quantity), GROUP BY, then HAVING. That returns 14 products with at least 500 lines. Their average discounts sit between 1.58% and 2.45%: most lines carry no coupon at all, so the average stays low.

Two conditions in one HAVING

HAVING accepts the same AND, OR and parentheses as WHERE, as long as each condition is about the group: an aggregate, or a column you grouped by.

HAVING COUNT(*) >= 500
   AND AVG(discount_pct) < 2

Six of the 14 also have an average discount under 2%. The busiest of them is product 154 with 836 lines.

You can repeat the aggregate in HAVING rather than using the alias. DuckDB also accepts HAVING lines >= 500, but many databases do not, so writing the aggregate again is the portable habit.

Common mistakes

• Putting AVG(discount_pct) < 2 in WHERE. Aggregates are not allowed in WHERE, because WHERE runs before the groups exist.
• Moving quantity > 0 into HAVING. It is a condition on single lines, so it belongs in WHERE; HAVING only sees groups.
• Joining the two conditions with OR. “At least 500 lines or a low discount” keeps every small seller with no coupons, which is the opposite of a bestseller list.
• Filtering on the rounded column. 1.99 rounds to 1.99 here, but a value like 1.996 would show as 2.00 and still pass, which looks like a bug to the reader.

In SQL Server

Same clauses and the same order. SQL Server never accepts a SELECT alias in HAVING, so repeat the aggregate. Watch the average too: AVG of an INT column returns an INT there, but discount_pct is a decimal, so 1.99 stays 1.99:

HAVING COUNT(*) >= 500 AND AVG(discount_pct) < 2