Customers Driving 80% of Revenue
Pareto analysis on payments (only status = 'succeeded' counts). Total each customer's revenue and sort customers by revenue descending, with ties broken by customer_id ascending. Walk down that list and keep customers until the cumulative share of total revenue reaches at least 80%. The customer who crosses 80% is included; nobody after them is.
- revenue: the customer's total, ROUND(x, 2)
- cumulative_pct: 100.0 * cumulative revenue / total revenue at that customer, ROUND(x, 2)
Return columns customer_id, revenue, cumulative_pct. Order by revenue descending, then customer_id ascending.
Schema
CREATE TABLE payments ( payment_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount REAL NOT NULL, status TEXT NOT NULL );
Sample data
payments
| payment_id | customer_id | amount | status |
|---|---|---|---|
| 1 | 1 | 500 | succeeded |
| 2 | 2 | 300 | succeeded |
| 3 | 1 | 100 | succeeded |
| 4 | 3 | 150 | succeeded |
| 5 | 4 | 80 | succeeded |
| 6 | 5 | 70 | succeeded |
| 7 | 2 | 900 | failed |
| 8 | 6 | 50 | refunded |
Hint 1
A running SUM over the sorted customers gives cumulative revenue; SUM(...) OVER () gives the grand total.
Hint 2
Keep a customer if the cumulative share before them was still under 80%. LAG gives you that previous value.
Hint 3
Use a ROWS frame so tied revenues do not get the same cumulative value.
Discussion 0
Join free to post in the discussion.
Good practice for: Data Analyst, Business Analyst, Data Scientist, Product Manager
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.