Top three products per category

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

A shop wants to know its best-selling products in every category. First add up the revenue for each product, then rank the products inside their category, and keep only the top three of each category. Using lb_orders, calculate the total revenue (the sum of amount) for each category and product. Rank products within each category by total_revenue from highest to lowest using RANK(). Return the rows with a rank of 3 or less, with the columns category (from product_category), product, total_revenue and rank. Sort by category, rank and product.

Requirements

• Add up amount for every (category, product) pair.
• Rank products inside each category by revenue, from highest to lowest, with RANK().
• Keep rows where the rank is 3 or less.
• Name the columns category, product, total_revenue and rank.
• Sort by category, rank and product.

Constraints

• The table is lb_orders.
• First aggregate (GROUP BY), then rank. Use two CTEs, or a CTE and a subquery.
• Use RANK() OVER (PARTITION BY category ORDER BY total_revenue DESC).

Examples

Input: lb_orders (product totals) category | product | total_revenue Electronics | Laptop | 1700 Electronics | Widget | 105 Electronics | Mouse | 40 Clothing | Shirt | 35 Output: category | product | total_revenue | rank Clothing | Shirt | 35 | 1 Electronics | Laptop | 1700 | 1 Electronics | Widget | 105 | 2 Electronics | Mouse | 40 | 3 Why this passes: RANK keeps up to three products per category. Clothing has only Shirt.

Topics: lakebench, sql, top-n, rank.

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