Business Problem

Microsoft's regional sales ops manager needs to run this month's commission payout and wants to stop doing it by hand in spreadsheets. For every sales rep, total up their closed sales for the period, then apply the company's tiered commission structure — a higher percentage kicks in once a rep crosses certain revenue thresholds — to calculate the exact commission owed along with which tier they landed in. Payroll needs this to reconcile against what reps are expecting before checks go out at the end of the week.

Dataset & Schema

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)
);

SQL Solution

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;

Explanation