Top three products and the rest as Others

Medium

Question

The sales table has one row for each sale: the product and the amount in rupees. A product has many sales.

A pie chart should show the 3 products with the highest total sales, and all other products together as one slice named Others. If two products have the same total and only one of them fits in the top 3, the one that comes first in A to Z order wins. If there are 3 products or fewer, there is no Others slice.

Return product (the product name, or Others) and revenue (the total amount). Sort by revenue from high to low for the 3 products, using the same A to Z rule for equal totals, and put Others in the last row, even when its total is bigger.

The order of the rows matters for this question.

top n
window function
row number
case
group by
rollup
ties
cte

Read first: Lesson 48, SQL patterns that interviews repeat

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

sales3 columns · 11 rowsnot run yet

CREATE TABLE sales (id INTEGER PRIMARY KEY, product TEXT NOT NULL, amount INTEGER NOT NULL);

Example rows of the sales table
id	product	amount
1	Rice	5000
2	Rice	4000
3	Oil	7500
4	Dal	3500
5	Dal	2500
6	Ghee	6000
7	Tea	1500
8	Tea	2500
9	Sugar	2500
10	Salt	600
11	Salt	400

About this SQL

First add up the sales of each product, and only then rank, because the rank is for products, not for sales rows. ROW_NUMBER gives every row a different number, so ties are cut by the extra column in ORDER BY. RANK would give the same number to equal totals and could let 4 products into the top 3. A CASE expression can also be used as the GROUP BY key, which is how many rows become one named group.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready