hardWindow Functions JOINs Duplicate handling

Top Three Per Category

Return the top three products by revenue per category, including ties.

Problem statement

Prompt

Given the category, product, and sale tables below, return the top three products by total revenue within each category.

Revenue of a product is SUM(quantity * unit_price) over all its sales.
A product appears in the result if it ranks in the top three within its category.
Ties are allowed and must not skip ranks: if two products tie for third place, both must appear (a dense ranking).
A category with fewer than three products returns only the products it has.
A product with no sales has revenue 0 and may still appear if it ranks in the top three.
Output columns: category (name), product (name), revenue (rounded to a whole number with ROUND(...)).

Example

category		product		sale
id	name	id	name	id	product_id	unit_price	quantity
1	Tech	1	Keyboard	1	1	50	2
		2	Mouse	2	2	25	4
		3	Screen

Tech revenue: Keyboard 100, Mouse 100, Screen 0.

Expected result:

category	product	revenue
Tech	Keyboard	100
Tech	Mouse	100
Tech	Screen	0

Constraints

category.id, product.id, and sale.id are primary keys.
product.category_id references category.id; sale.product_id references product.id.

Tips

Aggregate revenue first (SUM(quantity * unit_price) grouped by product), then rank the aggregated rows with DENSE_RANK() OVER (PARTITION BY category ...).
Joining product to category is a pure lookup; it must not change the row grain — start from product so products without sales still exist.
ROUND() keeps the result tidy; the reference compares rounded values.

Schema

CREATE TABLE category (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE product (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    category_id INTEGER NOT NULL REFERENCES category(id)
);

CREATE TABLE sale (
    id         INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES product(id),
    unit_price REAL NOT NULL,
    quantity   INTEGER NOT NULL
);

Sample data

INSERT INTO category (id, name) VALUES
  (1, 'Tech'),
  (2, 'Office'),
  (3, 'Furniture');

INSERT INTO product (id, name, category_id) VALUES
  (1, 'Keyboard', 1),
  (2, 'Mouse',    1),
  (3, 'Monitor',  1),
  (4, 'Chair',    3),
  (5, 'Desk',     3),
  (6, 'Lamp',     3),
  (7, 'Pen',      2),
  (8, 'Notebook', 2),
  (9, 'Stapler',  2);

INSERT INTO sale (id, product_id, unit_price, quantity) VALUES
  (1,  1, 50, 2),    -- Keyboard 100
  (2,  1, 50, 1),    -- Keyboard 50
  (3,  2, 25, 4),    -- Mouse    100
  (4,  3, 200, 1),   -- Monitor  200
  (5,  4, 150, 2),   -- Chair    300
  (6,  5, 250, 1),   -- Desk     250
  (7,  6, 30,  1),   -- Lamp     30
  (8,  7, 2,   50),  -- Pen      100
  (9,  8, 4,   20),  -- Notebook 80
  (10, 9, 8,   5);   -- Stapler  40

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: dense top 3 by revenue per category, ties included.
WITH product_revenue AS (
    SELECT p.id AS product_id,
           p.name AS product_name,
           p.category_id,
           ROUND(SUM(s.quantity * s.unit_price)) AS revenue
    FROM product p
    LEFT JOIN sale s ON s.product_id = p.id
    GROUP BY p.id, p.name, p.category_id
),
ranked AS (
    SELECT product_name, category_id, revenue,
           DENSE_RANK() OVER (
               PARTITION BY category_id
               ORDER BY revenue DESC
           ) AS rnk
    FROM product_revenue
)
SELECT c.name AS category, r.product_name AS product, r.revenue
FROM ranked r
JOIN category c ON c.id = r.category_id
WHERE r.rnk <= 3
ORDER BY c.name, r.revenue DESC, r.product_name;

Key concepts

DENSE_RANK
PARTITION BY
INNER JOIN
Aggregation
Tie handling