The Drink Was Missing
Promo discounts land on the whole order. Refunds land on single items. Finance wants both stories told correctly.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
3
Business needs the model must satisfy.
You run analytics for a food-delivery app. The order feed lands three tables every night: checkouts with any promo discount applied to the whole order, the individual dishes and drinks with their menu prices and categories, and refunds that support issues when something goes wrong with a specific item, like a missing drink. Finance wants a warehouse model they can query without touching the raw feed. Design the model, write the load, and expose the two views they asked for.
The first decision in any model is what one row means, and this feed punishes the lazy answer. Interviewers use exactly this shape to see whether you pick a grain from the questions the business asks rather than from the shape the source arrives in.
- Design any model you like, load the feed into it, and answer through views. The views are the contract; the shape underneath is your call.
- Report net revenue by category: menu price, minus the promo share that fairly belongs to each item, minus its refunds.
- Report the net total per order after discounts and refunds.
- A typical order carries one to four items across a small fixed set of categories.
- Promo discounts apply to the whole order at checkout; items carry only their menu price.
- Refunds reference a specific item in a specific order and can arrive days later.
- What is net revenue by category, with promo discounts shared fairly across the items they paid for?
- What did each order net after discounts and refunds?
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.