SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.
Aggregate lb_orders revenue by product_category and product. Rank products within each category by total_revenue descending using RANK(). Return rows where rank <= 3 with columns: category (product_category), product, total_revenue, rank. Order by category, rank, product.
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
Interview-style drill: Rank products by revenue within each category and keep top 3.
Aggregate `lb_orders` revenue by `product_category` and `product`. Rank products within each category by `total_revenue` descending using RANK(). Return rows where rank <= 3 with columns: `category` (product_category), `product`, `total_revenue`, `rank`. Order by `category`, `rank`, `product`.