ORIGINAL TECHNICAL PRACTICE
Choose the correct top-N semantics
How would you return the top three salespeople per region when ties are possible?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Choose the ranking function from the business rule. ROW_NUMBER selects a fixed number of rows, RANK preserves ties with gaps, and DENSE_RANK preserves ties without gaps between distinct values.
Work through it
- Aggregate sales at one row per salesperson and region before ranking. Define the reporting window and treatment of refunds.
- Use a window partitioned by region and ordered by sales descending. Choose ROW_NUMBER<=3 for three deterministic people, adding a tie-breaker, or DENSE_RANK<=3 for the top three distinct sales totals.
- Walk through totals100,100,90,80 and verify that the result matches the intended rule. Filter the ranking in an outer query or a dialect-supported equivalent.
Tradeoffs to discuss
- Including all ties can return more than three people. A fixed-row result requires a documented secondary ordering that may be arbitrary from a business perspective.
Common pitfalls
- Adding a unique ID to a tie-preserving ranking’s ordering removes ties. Ranking raw transaction rows rather than per-person totals answers a different question.