logo

Back to questions

Sales Team Compensation

Medium

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:

  • Hitting a target means the amount of total deals is equivalent to or higher than the quota.
  • When a salesperson did not hit the target (quota), the employee receives a fixed salary and a commission on the total deals.
  • When a salesperson hits the target (quota), the compensation package includes:
    • a fixed base,
    • a regular commission on quota hit, and
    • a regular commission and accelerated commission on the balance of quota hit (total deals - quota) (see example output & explanation below).

Table:

Column NameType
employee_idinteger
baseinteger
commissiondouble
quotainteger
acceleratordouble

Example Input:

employee_idbasecommissionquotaaccelerator
101600000.15000001.5
102500000.14000001.5

Table:

Column NameType
employee_idinteger
deal_sizeinteger

Example Input:

employee_iddeal_size
101400000
101400000
102100000
102200000

Example Output:

employee_idtotal_compensation
101155000
10280000

Explanation

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)

PostgreSQL 14

Refer friends to get bonus content & cool prizes.