Paid at Yesterday's Rate
Reps get raises. Sales keep landing. Every commission has to be paid at the rate that was true on the day of the sale.
Concept
slowly-changing-dimensions
The primary modeling idea this problem reinforces.
Requirements
3
Business needs the model must satisfy.
A sales team earns commission on every deal, and rates change when reps are promoted or renegotiate. The feed sends two tables: closed sales with their dates and amounts, and rate changes, each saying that a rep moved to a new rate on a given day. Payroll needs commission per rep per month, valued at whatever rate was in force on the day each sale closed. Ops separately wants a simple roster of everyone with their rate as of now. Design the model, load the feed, and expose both views.
Values that change over time are where most warehouse models quietly go wrong, because the current value answers most questions until the day it answers one wrongly. Interviewers probe this constantly: the model has to know what was true then, not only what is true now.
- Design any model you like, load the feed into it, and answer through views. The shape underneath is your call.
- Report commission per rep per month, each sale valued at the rate in force on its sale date.
- Report the current rate roster: every rep and the rate they hold today.
- Rate changes are events: a rep, a day it takes effect, and the new rate. No end dates are sent.
- A rep can change rates at any time, including between two of their own sales in the same month.
- Sales volume is steady; rate changes are rare but decisive.
- How much commission does each rep earn per month, valued at the rate in force on each sale date?
- What rate does every rep hold right now?
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.