Products making 80% of revenue

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

product_sales contains revenue figures with columns product_id, product_name, and revenue. Find the smallest subset of highest-earning products whose cumulative sales reach or exceed 80% of total revenue (Pareto analysis). Return product_id, product_name, revenue, and cumulative_pct (rounded to 2 decimal places), sorted by revenue descending and product_id ascending.

Requirements

• Output columns: product_id, product_name, revenue, cumulative_pct.
• Order by revenue descending, product_id ascending.

Constraints

• revenue is a positive number.
• product_id is unique.
• cumulative_pct is computed as running_revenue * 100.0 / total_revenue.

Examples

Input: product_sales product_id,product_name,revenue P1,Laptop Pro,50000 P2,Phone Ultra,30000 P3,Wireless Earbuds,10000 P4,USB Cable,5000 P5,Laptop Stand,3000 P6,Mouse Pad,2000 Output: product_id | product_name | revenue | cumulative_pct P1 | Laptop Pro | 50000 | 50 P2 | Phone Ultra | 30000 | 80 Why this passes: Laptop Pro (50%) plus Phone Ultra (cumulative 80%) reach the 80% threshold, so the smaller products are not needed.

Input: product_sales product_id | product_name | revenue A | Super | 80 B | Other | 20 Output: product_id | product_name | revenue | cumulative_pct A | Super | 80 | 80 Why this passes: Product A hits exactly 80%; prior cumulative sum was 0, so only A is required.

Topics: lakebench, sql, pareto, cumulative-share.

More SQL interview questions · All interview problems · Learn data engineering