Top Three Sellers per Category in Q4, With Ties

Hard
119.

Top Three Sellers per Category in Q4, With Ties

For the festive-season awards, each category honours its top sellers by revenue (quantity * unit_price) over order lines dated in Q4 2024 (1 October to 31 December). Sellers with equal revenue share a rank, and ranks are dense — after two sellers tied at rank 1 the next is rank 2. Every seller ranked 1, 2 or 3 is honoured, however many that is.

Return category, store_name, revenue and category_rank, ordered by category, then category_rank, then store_name. Lines outside Q4 are ignored; a seller's revenue in a category counts only their lines in that category.

Tables

Table: sellers

ColumnType
seller_idint
store_namevarchar

Primary key: seller_id.

One row per seller; store names are unique.

Table: order_lines

ColumnType
line_idint
seller_idint
categoryvarchar
quantityint
unit_priceint
order_datedate

Primary key: line_id.

One row per delivered order line; seller_id is always in sellers.

Example

The rows must come back in the order shown.

sellers

seller_idstore_name
1UrbanKart
2StyleStreet
3Lakshmi Textiles
4Patel Handlooms
5GadgetHub

order_lines

line_idseller_idcategoryquantityunit_priceorder_date
11Fashion215002024-10-05
22Fashion130002024-11-11
33Fashion120002024-12-01
44Fashion35002024-10-20
54Fashion240002024-09-30
65Fashion19002024-12-31
75Electronics225002024-11-02
81Electronics149992025-01-01

Output

categorystore_namerevenuecategory_rank
ElectronicsGadgetHub50001
FashionStyleStreet30001
FashionUrbanKart30001
FashionLakshmi Textiles20002
FashionPatel Handlooms15003
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Top Three Sellers per Category in Q4, With Ties

For the festive-season awards, each category honours its top sellers by revenue (quantity * unit_price) over order lines dated in Q4 2024 (1 October to 31…

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #119

Problem statement

For the festive-season awards, each category honours its top sellers by revenue (quantity * unit_price) over order lines dated in Q4 2024 (1 October to 31 December). Sellers with equal revenue share a rank, and ranks are dense — after two sellers tied at rank 1 the next is rank 2. Every seller ranked 1, 2 or 3 is honoured, however many that is.

Return category, store_name, revenue and category_rank, ordered by category, then category_rank, then store_name. Lines outside Q4 are ignored; a seller's revenue in a category counts only their lines in that category.

Tables

Table: sellers

ColumnType
seller_idint
store_namevarchar

Primary key: seller_id.

One row per seller; store names are unique.

Table: order_lines

ColumnType
line_idint
seller_idint
categoryvarchar
quantityint
unit_priceint
order_datedate

Primary key: line_id.

One row per delivered order line; seller_id is always in sellers.

Examples

Example 1

sellers

seller_idstore_name
1UrbanKart
2StyleStreet
3Lakshmi Textiles
4Patel Handlooms
5GadgetHub

order_lines

line_idseller_idcategoryquantityunit_priceorder_date
11Fashion215002024-10-05
22Fashion130002024-11-11
33Fashion120002024-12-01
44Fashion35002024-10-20
54Fashion240002024-09-30
65Fashion19002024-12-31
75Electronics225002024-11-02
81Electronics149992025-01-01

Output

categorystore_namerevenuecategory_rank
ElectronicsGadgetHub50001
FashionStyleStreet30001
FashionUrbanKart30001
FashionLakshmi Textiles20002
FashionPatel Handlooms15003

How to solve Top Three Sellers per Category in Q4, With Ties

First reduce the lines to revenue per seller per category, keeping only Q4 dates. The date filter is inclusive at both ends — 1 October and 31 December count, 30 September and 1 January do not — and it runs before grouping, so Patel Handlooms' September line in the example adds nothing.

Then rank inside each category with DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) and keep ranks up to 3. The choice of ranking function is the heart of the question: ROW_NUMBER() would honour exactly three sellers and break ties arbitrarily; RANK() would give 1, 1, 3 and stop a seller from being "second"; DENSE_RANK() gives 1, 1, 2, 3 — UrbanKart and StyleStreet both earn ₹3,000 in Fashion and share rank 1, so four Fashion sellers are honoured.

The correlated alternative computes the dense rank by definition: one plus the number of distinct revenues strictly above. It is quadratic per category but needs no window functions. The final ORDER BY names the store as the last key so tied sellers come out in a fixed order.

Reference solution (MySQL)

WITH revenue AS (
  SELECT l.category, s.store_name, SUM(l.quantity * l.unit_price) AS revenue
  FROM order_lines l
  JOIN sellers s ON s.seller_id = l.seller_id
  WHERE l.order_date BETWEEN '2024-10-01' AND '2024-12-31'
  GROUP BY l.category, s.seller_id, s.store_name
), ranked AS (
  SELECT category, store_name, revenue,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
  FROM revenue
)
SELECT category, store_name, revenue, category_rank
FROM ranked
WHERE category_rank <= 3
ORDER BY category, category_rank, store_name

Another way

WITH revenue AS (
  SELECT l.category, s.store_name, SUM(l.quantity * l.unit_price) AS revenue
  FROM order_lines l JOIN sellers s ON s.seller_id = l.seller_id
  WHERE YEAR(l.order_date) = 2024 AND QUARTER(l.order_date) = 4
  GROUP BY l.category, s.seller_id, s.store_name
)
SELECT category, store_name, revenue, category_rank FROM (
  SELECT r.category, r.store_name, r.revenue,
         (SELECT COUNT(DISTINCT x.revenue) FROM revenue x WHERE x.category = r.category AND x.revenue > r.revenue) + 1 AS category_rank
  FROM revenue r
) t
WHERE category_rank <= 3
ORDER BY category, category_rank, store_name

← Median Delivery Days per Courier Partner · Longest Daily Selling Streak per Seller →