MiddleCodeCommonNot answered yet
Top-10 RU customers by cart total above a threshold
Tables customer(id, email, country) and cart_item(id, customer_id, title, amount, price). Return the id and email of the top-10 customers in Russia by total cart value.
Requirements:
- filter to
country = 'ru' - each customer's total is
SUM(amount * price)across their cart items - keep only customers whose total is
>= 1000 - the total threshold is a post-aggregation filter, not a row filter
- order by the total descending, return at most 10 rows
SELECT customer.id, customer.email
FROM customer
-- your query hereWrite the query.
Filter to RU rows in WHERE country = 'ru', GROUP BY customer.id, email, then sum each cart with SUM(amount * price). HAVING filters groups whose total is >= 1000 (it runs after aggregation, unlike WHERE), ORDER BY that sum DESC, and LIMIT 10 keeps the top rows.
- ✗Putting the aggregate
SUM(...) >= 1000test inWHERE, which runs before grouping and rejects it - ✗Forgetting every non-aggregated selected column must appear in
GROUP BY - ✗Using
INNER JOINand silently dropping RU customers who have an empty cart
- →Why must the cart total go in
HAVINGand not in theWHEREclause? - →How would you also return each customer's item count alongside the total?
Contents
Schema
CREATE TABLE customer (
id INTEGER PRIMARY KEY,
email VARCHAR(100) NOT NULL,
country CHAR(2) NOT NULL
);
CREATE TABLE cart_item (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
title VARCHAR(20) NOT NULL,
amount INTEGER NOT NULL,
price INTEGER NOT NULL
);Task: return the id and email of the ten customers in Russia (country = 'ru') whose total cart value (SUM(amount * price)) is at least 1000, ordered by that total descending.
Solution
SELECT customer.id, customer.email
FROM customer
LEFT JOIN cart_item ON cart_item.customer_id = customer.id
WHERE customer.country = 'ru'
GROUP BY customer.id, customer.email
HAVING SUM(cart_item.amount * cart_item.price) >= 1000
ORDER BY SUM(cart_item.amount * cart_item.price) DESC
LIMIT 10;Key points:
and is dropped by HAVING (the >= 1000 test is false for NULL).
WHEREfilters rows before grouping, so the country filter belongs here.HAVINGfilters groups after aggregation — the only place you can compareSUM(...).- A non-aggregated column in
SELECTis legal only when it appears inGROUP BY. LEFT JOINkeeps customers with an empty cart; their sum isNULL
Contents
