Sales Team Compensation
As an analyst on the Sales-Operations team, you are helping the VP of Sales determine the final compensation each salesperson earned for the year.
Each salesperson earns a fixed base salary and a percentage of commission on their total deals. Also, if they beat their quota, any sales after that receive an accelerator, which is just a higher commission rate applied to their commissions after they hit the quota.
Based on the aforementioned, write a query to calculate the total compensation earned by each salesperson. Output the employee id and total compensation in descending order.
Assumptions:
Column Name | Type |
---|---|
employee_id | integer |
base | integer |
commission | double |
quota | integer |
accelerator | double |
employee_id | base | commission | quota | accelerator |
---|---|---|---|---|
101 | 60000 | 0.1 | 500000 | 1.5 |
102 | 50000 | 0.1 | 400000 | 1.5 |
Column Name | Type |
---|---|
employee_id | integer |
deal_size | integer |
employee_id | deal_size |
---|---|
101 | 400000 |
101 | 400000 |
102 | 100000 |
102 | 200000 |
employee_id | total_compensation |
---|---|
101 | 155000 |
102 | 80000 |
ID 101: 60,000 + 50,000 (this is 10% of their 500k since they hit the quota) + 45,000 (they did 300k over quota * 10% commission * 1.5 accelerator)
ID 102: 50,000 (base) + 30,000 (commission on 300,000 of total deals)
As the salaries are dependent on the total deals, we have to first do a bit of data preprocessing and calculate the sum of deals for each employee.
To do so, simply sum up the individual deals, and wrap the query in a CTE to use the dataset later.
Output showing the first 5 rows:
employee_id | total_deals |
---|---|
101 | 800000 |
103 | 350000 |
104 | 150000 |
105 | 440000 |
102 | 300000 |
As the next step, we need to join the aggregated deals dataset with employee contract dataset, so employee performance could be compared against the set quotas.
Only now can we start building the logic into a query. According to the task, we have 2 situations that we need to implement:
Employee underperforms and does not hit the quota:
Total compensation = Fixed base salary + (Commission x Total deals)
Employee hits the quota:
Total compensation = Fixed base salary + (Commission x Quota) + ((Total deals - Quota) x Commission x Accelerator)
To do this, we simply use a statement that checks the amount of total deals against the quota, and respectively returns output using either first or second formula.
PostgreSQL 14