You are asked to design a SQL solution for 'the top 3 products by revenue in each category'. How do you start?
You write down what the answer should look like, and you settle the questions that change the meaning, before you touch the schema.
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
category_id INTEGER NOT NULL,
name TEXT NOT NULL,
unit_price_cents INTEGER NOT NULL
);
CREATE TABLE order_lines (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL
);
So what are the questions that change the meaning? Here are the ones that come up every time.
Is "top" measured by the revenue or by the number of units sold? Those two give different answers for a cheap product that sells a lot and an expensive one that sells a little.
Do the ties count? If four products have the same revenue and you want three, then you either get three or four, and the difference between the two answers is the function ROW_NUMBER and the function RANK.
Which time period? This month, this year, or everything? And does a cancelled order count?
What happens to a product that has never sold? It has no revenue at all, and if you leave it out of the answer then a reader cannot tell the difference between a new product and a missing one.
And is the category list flat, or does it have a parent? Because if it has a parent then "in each category" might mean the top level only, or the top in each branch of the tree.
Once those are settled, the query itself is short.
-- The top three products by revenue in each category.
WITH revenue AS (
SELECT
p.category_id,
p.id AS product_id,
p.name AS product_name,
SUM(l.quantity * p.unit_price_cents) AS revenue
FROM order_lines l
JOIN products p ON p.id = l.product_id
GROUP BY p.category_id, p.id, p.name
),
ranked AS (
SELECT
category_id,
product_name,
revenue,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS position
FROM revenue
)
SELECT
c.name AS category_name,
r.product_name,
r.revenue,
r.position
FROM ranked r
JOIN categories c ON c.id = r.category_id
WHERE r.position <= 3
ORDER BY c.name, r.position, r.product_name;
So the rule to remember is this. Settle the meaning first, because every one of those questions changes the SQL, and it is much cheaper to answer them before you have written anything.
The next question in this topic looks at the clarifying questions on their own, and the last one looks at a slow report.