Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Microsoft
Calculate total sales per employee and determine commission based on sales amount (commission tiers).
Payroll needs March commissions for five salespeople under a tiered plan: total sales up to ₹1L earn 5%, up to ₹1.5L earn 6%, up to ₹2L earn 8%, and ₹2L+ earns 10% — applied to the entire total once a threshold is crossed (a cliff, not a marginal slab).
Per employee the query returns sale count, total sales amount, the commission amount, and the tier label, richest first. The output doubles as a plan audit: it shows exactly how concentrated rewards are (do two reps take 70% of the pool?), whether the base tier motivates or demoralizes the bottom, and where the thresholds create perverse incentives — timing games (holding a December mega-deal for January) and deal-splitting across tier boundaries.
A correct query that can't discuss gaming and fairness answers the payroll question while missing the business one.
Employees with employee_id, employee_name, department; sales with sale_id, employee_id, sale_amount, sale_date.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT,
department TEXT
);
CREATE TABLE employee_sales (
sale_id INTEGER PRIMARY KEY,
employee_id INTEGER,
sale_amount DECIMAL(10,2),
sale_date DATE,
FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
);Your target
Calculate total sales per employee and determine commission based on sales amount (commission tiers).
Loading the expected output…
SELECT
e.employee_id,
e.employee_name,
e.department,
COUNT(es.sale_id) as total_sales_count,
ROUND(SUM(es.sale_amount), 2) as total_sales_amount,
CASE
WHEN SUM(es.sale_amount) >= 200000 THEN ROUND(SUM(es.sale_amount) * 0.10, 2)
WHEN SUM(es.sale_amount) >= 150000 THEN ROUND(SUM(es.sale_amount) * 0.08, 2)
WHEN SUM(es.sale_amount) >= 100000 THEN ROUND(SUM(es.sale_amount) * 0.06, 2)
ELSE ROUND(SUM(es.sale_amount) * 0.05, 2)
END as commission_amount,
CASE
WHEN SUM(es.sale_amount) >= 200000 THEN '10%'
WHEN SUM(es.sale_amount) >= 150000 THEN '8%'
WHEN SUM(es.sale_amount) >= 100000 THEN '6%'
ELSE '5%'
END as commission_tier
FROM employees e
LEFT JOIN employee_sales es ON e.employee_id = es.employee_id
GROUP BY e.employee_id, e.employee_name, e.department
ORDER BY total_sales_amount DESC;Step-by-step Solution:
SELECT employee details: employee_id, employee_name, department.
Count the number of sales using COUNT(es.sale_id) to show activity level.
Sum total sales using SUM(es.sale_amount) to get each employee's total revenue.
Use CASE statement to calculate commission_amount based on tier: >=₹200K gets 10%, >=₹150K gets 8%, >=₹100K gets 6%, else 5%. Multiply SUM(es.sale_amount) by the tier percentage.
Use another CASE to display commission_tier as text ('10%', '8%', etc.).
LEFT JOIN employees with employee_sales to connect each employee with their sales. GROUP BY employee_id, name, department to aggregate per employee.
ORDER BY total_sales_amount DESC to show top performers first. This enables transparent commission calculation and recognizes high performers.
Computing the reference output…
March commission payroll totals Rs.52,850, led by Anjali Verma (Rs.2.1L sales, top 10% tier, Rs.21,000 payout). The top two reps earn ~70% of the pool while Divya Kapoor trails at the 5% base tier (Rs.55K sales) — the tier thresholds are doing their job of concentrating rewards on quota-busters.
What the interviewer asks next
“Ramesh closed 3 sales — as many as the leader — but earns a third of her commission. Fair?”
Strong answer covers: Activity vs output: the plan explicitly rewards revenue concentration over deal count; defend or attack that choice, and name who it motivates vs who it demoralizes (Divya at the 5% base).
“What stops a rep from holding a December mega-deal for January, or splitting one deal into pieces across tiers?”
Strong answer covers: Gaming the plan: accelerators invite timing games and tier thresholds invite deal-splitting — propose guards (booking rules, audit flags, decelerators) alongside the payout math.
“The top two reps take ~70% of the Rs.52,850 pool — is that concentration a feature or a bug?”
Strong answer covers: Plan philosophy: steep tiers retain stars but starve the middle; argue from what the business needs this year (retention of top talent vs broad-based motivation).
“Suresh earns more (Rs.15,200) on 2 sales than Ramesh (Rs.7,500) on 3 — the plan pays for size, not effort. What behavior does that breed across the team?”
Strong answer covers: Incentive-behavior mapping: reps learn to chase elephants and neglect mice — small-deal coverage rots, and the long tail of customers feels it first.
“Arjun (Rs.1.4L, 2 sales) and Ramesh (Rs.1.25L, 3 sales) share the 6% tier, yet Arjun was clearly more efficient. Should efficiency earn anything?”
Strong answer covers: Missing dimension: the plan is blind to cost-of-sale (calls, travel, time) — a per-deal efficiency kicker would reward Arjun-style selling without touching the tier structure.
“Divya sits at Rs.55K — below the first meaningful tier. Draw versus higher base rate: how do you keep her without overpaying failure?”
Strong answer covers: Bottom-tier design: a recoverable draw protects new reps while preserving hunger; a flat 5% on crumbs guarantees either attrition or complacency — pick your poison explicitly.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.