Company Salary Post-Tax Adjustment

Medium

A payroll analytics system stores compensation snapshots for multiple companies. Table: salaries company_id: company identifier employee_id: employee identifier within company employee_name: employee display name salary: gross salary before tax (company_id, employee_id) is unique Policy determination is company-level and depends only on that company's maximum salary: If max salary < 1000, tax rate is 0% (employee keeps 100%). If max salary is between 1000 and 10000 inclusive, tax rate is 24% (employee keeps 76%). If max salary > 10000, tax rate is 49% (employee keeps 51%). Task: For every employee row, compute salary after tax and round to the nearest integer. Output requirements: Return exactly these columns in this order: company_id employee_id employee_name salary The output may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

salaries:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 10         | 1           | Asha          | 2000   |
| 10         | 2           | Bruno         | 21300  |
| 10         | 3           | Chao          | 10800  |
| 20         | 1           | Daria         | 300    |
| 20         | 2           | Elias         | 450    |
| 20         | 3           | Farah         | 700    |
| 30         | 1           | Gina          | 100    |
| 30         | 2           | Hugo          | 2200   |
| 30         | 3           | Imani         | 3300   |
| 30         | 4           | Joon          | 7777   |

Output

[
  {"company_id":10,"employee_id":1,"employee_name":"Asha","salary":1020},
  {"company_id":10,"employee_id":2,"employee_name":"Bruno","salary":10863},
  {"company_id":10,"employee_id":3,"employee_name":"Chao","salary":5508},
  {"company_id":20,"employee_id":1,"employee_name":"Daria","salary":300},
  {"company_id":20,"employee_id":2,"employee_name":"Elias","salary":450},
  {"company_id":20,"employee_id":3,"employee_name":"Farah","salary":700},
  {"company_id":30,"employee_id":1,"employee_name":"Gina","salary":76},
  {"company_id":30,"employee_id":2,"employee_name":"Hugo","salary":1672},
  {"company_id":30,"employee_id":3,"employee_name":"Imani","salary":2508},
  {"company_id":30,"employee_id":4,"employee_name":"Joon","salary":5911}
]

Explanation

Company 10 uses 49% tax because its max salary is 21300. Company 20 uses 0% tax because its max is 700. Company 30 uses 24% tax because its max is 7777.

Example 2

Input

salaries:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 1          | 1           | Nia           | 999    |
| 1          | 2           | Omar          | 500    |
| 2          | 1           | Pia           | 1000   |
| 2          | 2           | Quin          | 875    |
| 3          | 1           | Rafi          | 10001  |
| 3          | 2           | Sara          | 2500   |

Output

[
  {"company_id":1,"employee_id":1,"employee_name":"Nia","salary":999},
  {"company_id":1,"employee_id":2,"employee_name":"Omar","salary":500},
  {"company_id":2,"employee_id":1,"employee_name":"Pia","salary":760},
  {"company_id":2,"employee_id":2,"employee_name":"Quin","salary":665},
  {"company_id":3,"employee_id":1,"employee_name":"Rafi","salary":5101},
  {"company_id":3,"employee_id":2,"employee_name":"Sara","salary":1275}
]

Explanation

This case exercises both policy boundaries: 999 remains untaxed, 1000 triggers 24% tax, and 10001 triggers 49% tax.

Example 3

Input

salaries:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 7          | 10          | Tia           | 10150  |
| 7          | 11          | Uma           | 250    |
| 7          | 12          | Vito          | 150    |
| 8          | 10          | Wen           | 10000  |
| 8          | 11          | Xiu           | 1000   |
| 8          | 12          | Yara          | 1250   |

Output

[
  {"company_id":7,"employee_id":10,"employee_name":"Tia","salary":5177},
  {"company_id":7,"employee_id":11,"employee_name":"Uma","salary":128},
  {"company_id":7,"employee_id":12,"employee_name":"Vito","salary":77},
  {"company_id":8,"employee_id":10,"employee_name":"Wen","salary":7600},
  {"company_id":8,"employee_id":11,"employee_name":"Xiu","salary":760},
  {"company_id":8,"employee_id":12,"employee_name":"Yara","salary":950}
]

Explanation

In company 7, high-band tax (49%) applies and 150 becomes 76.5, which rounds to 77. In company 8, mid-band tax (24%) applies to all rows.

Constraints

0 <= number of rows in salaries <= 300000
1 <= company_id <= 1000000000
1 <= employee_id <= 1000000000
1 <= salary <= 1000000
employee_name is a non-empty string
(company_id, employee_id) is unique
Return exactly four columns: company_id, employee_id, employee_name, salary
Per-company tax policy is based only on max salary in that company
Round adjusted salary to nearest integer
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [company_id, employee_id, employee_name, salary]

salaries: | company_id | employee_id | employee_name | salary | |------------|-------------|---------------|--------| | 10 | 1 | Asha | 2000 | | 10 | 2 | Bruno | 21300 | | 10 | 3 | Chao | 10800 | | 20 | 1 | Daria | 300 | | 20 | 2 | Elias | 450 | | 20 | 3 | Farah | 700 | | 30 | 1 | Gina | 100 | | 30 | 2 | Hugo | 2200 | | 30 | 3 | Imani | 3300 | | 30 | 4 | Joon | 7777 |

[ {"company_id":10,"employee_id":1,"employee_name":"Asha","salary":1020}, {"company_id":10,"employee_id":2,"employee_name":"Bruno","salary":10863}, {"company_id":10,"employee_id":3,"employee_name":"Chao","salary":5508}, {"company_id":20,"employee_id":1,"employee_name":"Daria","salary":300}, {"company_id":20,"employee_id":2,"employee_name":"Elias","salary":450}, {"company_id":20,"employee_id":3,"employee_name":"Farah","salary":700}, {"company_id":30,"employee_id":1,"employee_name":"Gina","salary":76}, {"company_id":30,"employee_id":2,"employee_name":"Hugo","salary":1672}, {"company_id":30,"employee_id":3,"employee_name":"Imani","salary":2508}, {"company_id":30,"employee_id":4,"employee_name":"Joon","salary":5911} ]

salaries: | company_id | employee_id | employee_name | salary | |------------|-------------|---------------|--------| | 1 | 1 | Nia | 999 | | 1 | 2 | Omar | 500 | | 2 | 1 | Pia | 1000 | | 2 | 2 | Quin | 875 | | 3 | 1 | Rafi | 10001 | | 3 | 2 | Sara | 2500 |

[ {"company_id":1,"employee_id":1,"employee_name":"Nia","salary":999}, {"company_id":1,"employee_id":2,"employee_name":"Omar","salary":500}, {"company_id":2,"employee_id":1,"employee_name":"Pia","salary":760}, {"company_id":2,"employee_id":2,"employee_name":"Quin","salary":665}, {"company_id":3,"employee_id":1,"employee_name":"Rafi","salary":5101}, {"company_id":3,"employee_id":2,"employee_name":"Sara","salary":1275} ]

salaries: | company_id | employee_id | employee_name | salary | |------------|-------------|---------------|--------| | 7 | 10 | Tia | 10150 | | 7 | 11 | Uma | 250 | | 7 | 12 | Vito | 150 | | 8 | 10 | Wen | 10000 | | 8 | 11 | Xiu | 1000 | | 8 | 12 | Yara | 1250 |

[ {"company_id":7,"employee_id":10,"employee_name":"Tia","salary":5177}, {"company_id":7,"employee_id":11,"employee_name":"Uma","salary":128}, {"company_id":7,"employee_id":12,"employee_name":"Vito","salary":77}, {"company_id":8,"employee_id":10,"employee_name":"Wen","salary":7600}, {"company_id":8,"employee_id":11,"employee_name":"Xiu","salary":760}, {"company_id":8,"employee_id":12,"employee_name":"Yara","salary":950} ]