Top-3 Products per Category with Ties

SQL practice question · Hard · Window Functions · Amazon, Flipkart

For each category, find the products with the top 3 *distinct* net revenue values. Net revenue = SUM(qty * unit_price) over order lines that were NOT returned (`is_returned = 0`). Products with no non-returned sales are ignored. Products with equal revenue share a rank and the next rank is not skipped (so a category can return more than 3 products). Return `category`, `product_name`, `net_revenue` (ROUND 2), `rnk`. Order by `category`, `rnk`, `product_name`.

Table: products

Schema of products
Column	Type
product_id	INTEGER
product_name	TEXT
category	TEXT

Schema of products

Column

Type

product_id

INTEGER

product_name

TEXT

Sample rows of products
product_id	product_name	category
1	Echo Mini	Electronics
2	Kindle Lite	Electronics
3	Fire Stick	Electronics
4	Smart Plug	Electronics
5	Yoga Mat	Fitness
6	Dumbbell Set	Fitness
7	Resistance Band	Fitness

Schema of order_items
Column	Type
order_id	INTEGER
product_id	INTEGER
qty	INTEGER
unit_price	REAL
is_returned	BOOLEAN

Sample rows of order_items
order_id	product_id	qty	unit_price	is_returned
1	1	2	40	0
2	2	1	90	0
3	3	3	30	0
4	4	4	15	0
5	1	1	40	1
6	5	2	25	0
7	6	1	60	0
8	7	5	8	0

Expected output on the sample data
category	product_name	net_revenue	rnk
Electronics	Fire Stick	90	1
Electronics	Kindle Lite	90	1
Electronics	Echo Mini	80	2
Electronics	Smart Plug	75	3
Fitness	Dumbbell Set	60	1
Fitness	Yoga Mat	50	2
Fitness	Resistance Band	40	3

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest

Questions

Companies

Solved

Bookmark Question Companies Difficulty Category Status

{{ r.n }} - {{ r.title }}

No questions match these filters.

Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.

Daily challenge

{{ daily.title }} · {{ daily.difficulty }} Solve now

New question every day at midnight IST.

Your Progress

Reset all SQL practice progress{{ resetScope }}? This can't be undone.

{{ p.label }} ({{ p.pct }}%){{ p.solved }}/{{ p.total }}

Points: {{ myPoints }} · Easy 10 · Medium 20 · Hard 40

🔥 Longest streak: {{ streak.longest }}Current streak: {{ streak.current }}

Leaderboard (rolling 100 days)

Loading…

{{ u.rank }} {{ u.name }}{{ u.points }} pts

← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}

Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide

{{ embedCode }}

Table: {{ tb.name }}

ColumnType

Expected output on the sample data:

No submissions yet. Press Evaluate to submit your query.

The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn.

{{ solutionSql }}

SQL dialect ⏱ {{ timerLabel }}

{{ fnHelpTitle }}
{{ fnHelpText }}

Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically

Output

{{ out.error }}

Expected (sample data)

Your output (sample data)

Run your query to see the result here.

🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.

Top-3 Products per Category with Ties

Table: products

Table: order_items

Company-wise SQL questions

{{ cur.n }} - {{ cur.title }}

To Track Your Progress, Log In / Sign Up