Customers who make the first 80 percent of revenue

Hard

Question

A shop wants to know which customers matter most. Add up the order amounts of each customer, and list the customers from the highest total to the lowest. If two customers have the same total, the name that comes first in the alphabet is listed first.

Return the smallest group of customers from the top of that list whose totals add up to at least 80 percent of the total revenue of all customers. In other words: a customer is included when the customers ABOVE them in the list add up to LESS than 80 percent of the total revenue. The customer who takes the sum over 80 percent is included.

Return customer and revenue (the total of the customer). The order of the rows does not matter.

window function
running total
pareto
cte
window frame

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.

orders3 columns · 11 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, amount INTEGER NOT NULL);

Example rows of the orders table
id	customer	amount
1	Asha	2000
2	Bilal	1000
3	Chen	1500
4	Asha	1500
5	Dara	400
6	Bilal	1000
7	Esha	500
8	Dara	300
9	Farid	500
10	Asha	700
11	Bilal	600

About this SQL

The frame ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING adds up the rows before the current one and leaves the current row out (SQLite 3.25 and later). For the first row there are no rows before it, so the sum is NULL, not 0: use COALESCE. An empty OVER () gives the total of the whole result next to each row. Compare with 0.8 * total instead of dividing to avoid whole number division.

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