How many products fit the budget?
SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.
budget_customers(customer_id, budget) holds customer shopping budgets. catalog_items(item_id, item_name, price) lists items for purchase. Each customer wants to purchase as many distinct items as possible, starting from the cheapest product in the catalog and buying ascending by price (tie-breaking by item_id). Once adding the next item would cause cumulative spend to exceed their budget, they stop purchasing. For each customer, return customer_id, budget, items_bought, and total_spent. Customers who cannot afford even the cheapest item must appear with items_bought = 0 and total_spent = 0. Order by customer_id ascending.
Requirements
- Output columns: customer_id, budget, items_bought, total_spent.
- Order by customer_id ascending.
Constraints
- price > 0.
- budget >= 0.
- Each customer can purchase at most 1 unit of each catalog item.
Examples
Input: budget_customers customer_id,budget C101,100 C102,30 C103,5 catalog_items item_id,item_name,price I1,Notebook,10 I2,Pen Set,15 I3,Desk Organizer,35 I4,Backpack,50 Output: customer_id | budget | items_bought | total_spent C101 | 100 | 3 | 60 C102 | 30 | 2 | 25 C103 | 5 | 0 | 0 Why this passes: Items are bought cheapest first while the budget lasts: 100 buys 10+15+35 = 60, 30 buys 10+15 = 25, and 5 buys nothing.
Input: budget_customers customer_id | budget C1 | 20 catalog_items item_id | item_name | price 1 | A | 10 2 | B | 10 Output: customer_id | budget | items_bought | total_spent C1 | 20 | 2 | 20 Why this passes: Customer C1 can afford both items for exactly 20.
Topics: lakebench, sql, running-total, threshold.
More SQL interview questions · All interview problems · Learn data engineering
How many products fit the budget?
Interview-style drill: Simulate a greedy budget purchase by selecting the cheapest products first until the balance is exhausted.
`budget_customers(customer_id, budget)` holds customer shopping budgets. `catalog_items(item_id, item_name, price)` lists items for purchase. Each customer wants to purchase as many distinct items as possible, starting from the cheapest product in the catalog and buying ascending by `price` (tie-breaking by `item_id`). Once adding the next item would cause cumulative spend to exceed their `budget`, they stop purchasing. For each customer, return `customer_id`, `budget`, `items_bought`, and `total_spent`. Customers who cannot afford even the cheapest item must appear with `items_bought = 0` and `total_spent = 0`. Order by `customer_id` ascending.