Calculate Salaries - Problem
🏢 You're working as a payroll specialist for a multi-company corporation! Each company has different tax brackets based on their highest-paid employee's salary.
Your Mission: Calculate the after-tax salaries for all employees across different companies, where the tax rate is determined by each company's maximum salary.
📊 Tax Brackets:
- 0% tax if company's max salary < $1,000
- 24% tax if company's max salary is between $1,000-$10,000 (inclusive)
- 49% tax if company's max salary > $10,000
Database Schema:
| Column Name | Type |
|---|---|
| company_id | int |
| employee_id | int |
| employee_name | varchar |
| salary | int |
⚠️ Important: Round all final salaries to the nearest integer and return results in any order.
Input & Output
basic_example.sql — SQL
$
Input:
Salaries table:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 1 | 1 | Tony | 2000 |
| 1 | 2 | Pronub | 21300 |
| 1 | 3 | Tyrrox | 10800 |
| 2 | 1 | Pam | 300 |
| 2 | 7 | Bassem | 450 |
| 2 | 9 | Hermione | 700 |
›
Output:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 1 | 1 | Tony | 1020 |
| 1 | 2 | Pronub | 10863 |
| 1 | 3 | Tyrrox | 5508 |
| 2 | 1 | Pam | 300 |
| 2 | 7 | Bassem | 450 |
| 2 | 9 | Hermione | 700 |
💡 Note:
Company 1: Max salary is 21300 (>10000), so 49% tax rate applies. Tony: 2000×0.51=1020, Pronub: 21300×0.51≈10863, Tyrrox: 10800×0.51=5508. Company 2: Max salary is 700 (<1000), so 0% tax rate applies - all salaries remain unchanged.
mid_tier_tax.sql — SQL
$
Input:
Salaries table:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 3 | 1 | Alice | 5000 |
| 3 | 2 | Bob | 8000 |
| 3 | 3 | Carol | 6500 |
›
Output:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 3 | 1 | Alice | 3800 |
| 3 | 2 | Bob | 6080 |
| 3 | 3 | Carol | 4940 |
💡 Note:
Company 3: Max salary is 8000 (between 1000-10000), so 24% tax rate applies. Alice: 5000×0.76=3800, Bob: 8000×0.76=6080, Carol: 6500×0.76=4940.
edge_case_boundary.sql — SQL
$
Input:
Salaries table:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 4 | 1 | Dave | 1000 |
| 4 | 2 | Eve | 900 |
| 5 | 1 | Frank | 10000 |
| 5 | 2 | Grace | 5000 |
›
Output:
| company_id | employee_id | employee_name | salary |
|------------|-------------|---------------|--------|
| 4 | 1 | Dave | 760 |
| 4 | 2 | Eve | 684 |
| 5 | 1 | Frank | 7600 |
| 5 | 2 | Grace | 3800 |
💡 Note:
Company 4: Max salary is exactly 1000 (boundary case), so 24% tax applies. Company 5: Max salary is exactly 10000 (boundary case), so 24% tax applies. Both companies fall within the [1000,10000] inclusive range.
Visualization
Tap to expand
Understanding the Visualization
1
Group by Company
Organize employees by their company_id to process each company separately
2
Find Company Max
For each company, identify the highest-paid employee's salary
3
Determine Tax Bracket
Based on company max: <1000→0%, 1000-10000→24%, >10000→49%
4
Apply Tax Rate
Multiply each employee's salary by (1 - tax_rate) and round to nearest integer
Key Takeaway
🎯 Key Insight: Tax rates are determined at the company level based on the highest earner, then applied uniformly to all employees within that company. Window functions make this calculation efficient by avoiding repeated scans of the data.
Time & Space Complexity
Time Complexity
O(n²)
For each of n employees, we scan all employees in the same company
⚠ Quadratic Growth
Space Complexity
O(1)
No additional space needed beyond the result set
✓ Linear Space
Constraints
- 1 ≤ employees count ≤ 104
- 1 ≤ company_id, employee_id ≤ 104
- 1 ≤ employee_name.length ≤ 20
- 1 ≤ salary ≤ 106
- (company_id, employee_id) is unique
- Round salary to nearest integer
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code