Lighthouse

Level 4 · Pareto: do 20% of products make 80% of sales?

From Thomas Whitaker, Head of Merchandising

“A consultant told us '80% of your sales come from 20% of your products, cut the long tail'. Before I cut anything, can you check it on our real numbers from 2023 to 2025?”

Your goal

The starter's product_sales CTE has product_id and net_sales (net sales of valid lines on booked orders, 2023 to 2025) for every product that sold. Return sales_rank (1 = highest net_sales; break ties by product_id), product_id, net_sales (rounded to 2) and cum_share_pct (the running total of net_sales up to and including this product × 100 / all net_sales, rounded to 2), one row per product, sorted by sales_rank.

Read from the orders tablenot done yet
Read from the order_items tablenot done yet
Show exactly sales_rank product_id net_sales cum_share_pctnot done yet

Cumulative share of net sales, products ranked from best seller

A strict 80/20 store would reach 80% at product 24. Harbor & Pine reaches it at product 56.

Show the numbers

Products, ranked by net sales	Cumulative share of net sales
1	6.7%
2	11%
3	14.6%
4	18.1%
5	20.8%
6	23.5%
7	25.9%
8	28.2%
9	30.4%
10	32.4%
11	34.4%
12	36.3%
13	38%
14	39.8%
15	41.4%
16	43%
17	44.5%
18	45.9%
19	47.3%
20	48.7%
21	50.1%
22	51.4%
23	52.6%
24	53.7%
25	54.9%
26	56.1%
27	57.2%
28	58.3%
29	59.4%
30	60.5%
31	61.5%
32	62.6%
33	63.6%
34	64.6%
35	65.6%
36	66.5%
37	67.3%
38	68.1%
39	68.9%
40	69.6%
41	70.4%
42	71.2%
43	71.9%
44	72.7%
45	73.4%
46	74.1%
47	74.8%
48	75.5%
49	76.2%
50	76.8%
51	77.5%
52	78.1%
53	78.7%
54	79.3%
55	79.9%
56	80.5%
57	81.1%
58	81.7%
59	82.2%
60	82.8%
61	83.4%
62	83.9%
63	84.5%
64	85.1%
65	85.6%
66	86.1%
67	86.6%
68	87.1%
69	87.6%
70	88.1%
71	88.6%
72	89.1%
73	89.6%
74	90%
75	90.4%
76	90.9%
77	91.2%
78	91.6%
79	92%
80	92.3%
81	92.7%
82	93%
83	93.3%
84	93.6%
85	93.9%
86	94.2%
87	94.5%
88	94.8%
89	95%
90	95.3%
91	95.5%
92	95.8%
93	96%
94	96.3%
95	96.5%
96	96.7%
97	96.9%
98	97.1%
99	97.3%
100	97.5%
101	97.7%
102	97.9%
103	98.1%
104	98.3%
105	98.5%
106	98.6%
107	98.8%
108	98.9%
109	99%
110	99.1%
111	99.2%
112	99.4%
113	99.5%
114	99.6%
115	99.7%
116	99.8%
117	99.8%
118	99.9%
119	100%
120	100%

Your query

SQL

Loading the editor…

Reference solution

ResultPreview of orders · press Run to see yours

Preview of orders
order_id	customer_id	order_date	status	channel
1001	125	2023-01-01 20:51:19	completed	web
1002	156	2023-01-02 07:58:46	completed	web
1003	22	2023-01-02 10:25:27	completed	marketplace
1004	48	2023-01-02 11:21:51	completed	marketplace

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

Tables

Harbor & Pine store data, ready to query.

orders12,000 rows · 7 columns

order_idinteger
customer_idinteger
order_datetimestamp
statusvarchar
channelvarchar
shipping_feedecimal
coupon_codevarchar

First 5 rows of orders
order_id	customer_id	order_date	status	channel	shipping_fee	coupon_code
1001	125	2023-01-01 20:51:19	completed	web	0.00	NULL
1002	156	2023-01-02 07:58:46	completed	web	0.00	NULL
1003	22	2023-01-02 10:25:27	completed	marketplace	4.99	WELCOME10
1004	48	2023-01-02 11:21:51	completed	marketplace	4.99	WELCOME10
1005	82	2023-01-02 11:43:36	completed	web	0.00	WELCOME10

order_items30,338 rows · 6 columns

order_item_idinteger
order_idinteger
product_idinteger
quantityinteger
unit_pricedecimal
discount_pctdecimal

First 5 rows of order_items
order_item_id	order_id	product_id	quantity	unit_price
1	1001	121	9	31.99
2	1001	154	3	11.99
3	1002	154	4	11.99
4	1002	194	1	149.00
5	1002	193	2	19.99

customers2,018 rows · 9 columns

customer_idinteger
first_namevarchar
last_namevarchar
emailvarchar
cityvarchar
statevarchar
signup_datedate
segmentvarchar
referral_sourcevarchar

First 5 rows of customers
customer_id	first_name	last_name	email	city	state	signup_date	segment	referral_source
1	Owen	Hudson	owen.hudson@icloud.com	San Francisco	CA	2022-06-02	consumer	search
2	Douglas	Mendoza	dmendoza69@gmail.com	Atlanta	GA	2022-06-02	consumer	NULL
3	Henry	Brooks	hbrooks@lakesidecafe.com	Los Angeles	CA	2022-06-04	business	NULL
4	Brian	Graham	bgraham@bluecedarhomes.com	Seattle	WA	2022-06-06	business	pinterest
5	Kyle	Jimenez	kjimenez27@yahoo.com	Indianapolis	IN	2022-06-07	consumer	NULL

products120 rows · 8 columns

product_idinteger
namevarchar
categoryvarchar
subcategoryvarchar
unit_pricedecimal
unit_costdecimal
launched_ondate
is_activeboolean

First 5 rows of products
product_id	name	category	subcategory	unit_price	unit_cost	launched_on	is_active
101	Vale Velvet Loveseat	Furniture	Sofas	1659.00	NULL	2023-09-11	true
102	Alder Oak Coffee Table	Furniture	Tables	919.00	503.50	2024-05-16	true
103	Orchard Rattan Dining Chair	Furniture	Chairs	789.00	429.87	2023-05-27	true
104	Upland Oak Bookcase	Furniture	Storage	429.00	214.70	2022-12-20	true
105	Avery Leather Bed Frame	Furniture	Beds	449.00	232.70	2021-08-31	true

returns1,673 rows · 5 columns

return_idinteger
order_item_idinteger
returned_ondate
reasonvarchar
refund_amountdecimal

First 5 rows of returns
return_id	order_item_id	returned_on	reason	refund_amount
1	14	2023-01-10	damaged	83.69
2	42	2023-01-10	wrong_item	19.99
3	105	2023-01-14	wrong_item	10.99
4	24	2023-01-16	changed_mind	26.99
5	126	2023-01-16	late	20.99

employees60 rows · 8 columns

employee_idinteger
first_namevarchar
last_namevarchar
departmentvarchar
titlevarchar
manager_idinteger
hire_datedate
salaryinteger

First 5 rows of employees
employee_id	first_name	last_name	department	title	manager_id	hire_date	salary
1	Catherine	Hale	Executive	Chief Executive Officer	NULL	2016-03-01	285000
2	Robert	Kim	Executive	Chief Operating Officer	1	2017-01-09	235000
3	Megan	Collins	Sales	Head of Sales	1	2018-04-16	172000
4	Chloe	Gutierrez	Sales	Account Executive	3	2020-01-21	72000
5	Frank	Bell	Sales	Account Executive	3	2023-05-21	73000

web_sessions40,000 rows · 7 columns

session_idinteger
customer_idinteger
started_attimestamp
devicevarchar
sourcevarchar
pages_viewedinteger
convertedboolean

First 5 rows of web_sessions
session_id	customer_id	started_at	device	source	pages_viewed	converted
1	NULL	2023-01-01 23:02:24	mobile	paid	1	false
2	18	2023-01-01 15:07:49	desktop	paid	1	false
3	NULL	2023-01-01 16:55:46	desktop	direct	7	false
4	NULL	2023-01-01 12:43:28	mobile	organic	3	false
5	NULL	2023-01-01 13:06:21	mobile	social	2	false

marketing_spend3,276 rows · 3 columns

spend_datedate
channelvarchar
amountdecimal

First 5 rows of marketing_spend
spend_date	channel	amount
2023-01-01	paid	492.99
2023-01-01	email	37.38
2023-01-01	social	200.53
2023-01-02	paid	709.13
2023-01-02	email	64.13

The lesson

The 80/20 rule is a claim, not a law

The Pareto principle says a small share of causes makes a large share of results: 20% of products, 80% of sales. It is often roughly true, and often quoted without checking. The check is simple and every analyst should be able to run it in a few lines: rank the products, add up their sales from the top, and see where the running total crosses 80%.

Running totals with window functions

A window function computes over a set of rows without collapsing them. With an ORDER BY inside OVER, SUM becomes a running total:

SUM(net_sales) OVER (ORDER BY net_sales DESC, product_id)   -- up to this row
SUM(net_sales) OVER ()                                       -- all rows

Divide the first by the second and you have the cumulative share. ROW_NUMBER() with the same ordering gives each product its rank.

Ties need a tie-breaker

By default an ordered window frame includes every row with the same ORDER BY value as the current one. Two products with identical sales would then share one running total and the curve would jump. Adding product_id to the ordering makes every row unique, so the ranks and the running totals line up one product at a time.

Reading the curve

The chart plots the cumulative share against the rank. A steep start means concentration. Here the curve is steep for the best sellers, then bends: the top 24 products make just over half of sales, and the 80% line is crossed at product 56. The last 64 products together make the final 20%, which is still over a million dollars across three years.

Common mistakes

Ranking by units instead of dollars. Cheap decor sells many units; the question is about sales.
Using the curve to cut products by itself. Margin, returns and what sells with what matter more than rank.
No tie-breaker in the window. Running totals repeat on ties.

Interview note

“How would you find the products that make 80% of revenue?” Describe exactly this: aggregate per product, running sum ordered by revenue with a tie-breaker, divide by the total, filter on the running share. Then add the business caveat about the tail.

Level 4 · Pareto: do 20% of products make 80% of sales?

Your goal

Your query

Reference solution

Mote checks your result

What Thomas gets, and why

Tables

The lesson

The 80/20 rule is a claim, not a law

Running totals with window functions

Ties need a tie-breaker

Reading the curve

Common mistakes

Interview note

Lantern lit!

Level 4 · Pareto: do 20% of products make 80% of sales?

The request

Your goal

Your query

Reference solution

Mote checks your result

What Thomas gets, and why

Tables

The lesson

The 80/20 rule is a claim, not a law

Running totals with window functions

Ties need a tie-breaker

Reading the curve

Common mistakes

Interview note

Cookie choices