Calculate Salaries - Problem

Given a table Salaries with employee information and their salaries, calculate the after-tax salaries for all employees.

The tax rate is determined by the maximum salary in each company:

  • 0% tax if max salary < $1000
  • 24% tax if max salary is between $1000-$10000 (inclusive)
  • 49% tax if max salary > $10000

Return the employee details with their after-tax salary rounded to the nearest integer.

Table Schema

Salaries
Column Name Type Description
company_id PK int Company identifier
employee_id PK int Employee identifier
employee_name varchar Employee name
salary int Employee salary before tax
Primary Key: (company_id, employee_id)
Note: Each row represents an employee with their company and salary information

Input & Output

Example 1 — Mixed Tax Brackets
Input Table:
company_id employee_id employee_name salary
1 1 Tony 2000
1 2 Pronub 21300
1 3 Tyson 10000
2 1 Pam 300
2 7 Bassem 450
Output:
company_id employee_id employee_name salary
1 1 Tony 1020
1 2 Pronub 10863
1 3 Tyson 5100
2 1 Pam 300
2 7 Bassem 450
💡 Note:

Company 1: Max salary is 21300 (>10000), so 49% tax applies. Tony: 2000 × 0.51 = 1020, Pronub: 21300 × 0.51 = 10863, Tyson: 10000 × 0.51 = 5100.

Company 2: Max salary is 450 (<1000), so 0% tax applies. All salaries remain unchanged.
Example 2 — Medium Tax Bracket
Input Table:
company_id employee_id employee_name salary
3 1 Alice 5000
3 2 Bob 8000
Output:
company_id employee_id employee_name salary
3 1 Alice 3800
3 2 Bob 6080
💡 Note:

Company 3: Max salary is 8000 (between 1000-10000), so 24% tax applies. Alice: 5000 × 0.76 = 3800, Bob: 8000 × 0.76 = 6080.

Constraints

  • 1 ≤ company_id ≤ 100
  • 1 ≤ employee_id ≤ 1000
  • 1 ≤ employee_name.length ≤ 100
  • 300 ≤ salary ≤ 100000

Visualization

Tap to expand
Calculate Salaries - Tax Rate Solution INPUT Salaries Table company_id 1 employee_id 1 salary $1000 company_id 1 employee_id 2 salary $15000 company_id 2 employee_id 3 salary $500 company_id 2 employee_id 4 salary $800 Tax Rate Rules max < $1000: 0% tax $1000 <= max <= $10000: 24% max > $10000: 49% tax ALGORITHM STEPS 1 Find Max Salary Per company, get MAX(salary) 2 Determine Tax Rate Apply rate based on max Company 1: max=15000 --> Rate = 49% Company 2: max=800 --> Rate = 0% 3 Calculate Net Salary salary * (1 - tax_rate) 4 Round Result ROUND to nearest integer ROUND(salary * (1-rate)) Using window function FINAL RESULT company emp salary 1 1 $510 1 2 $7650 2 3 $500 2 4 $800 Calculations Co.1: 1000*0.51=510, 15000*0.51=7650 Co.2: 0% tax, salaries unchanged OK Tax Applied Key Insight: Use a window function (MAX OVER PARTITION BY company_id) to find maximum salary per company without collapsing rows. Then use CASE WHEN to apply the correct tax rate bracket. This approach processes all employees in a single pass with O(n) time complexity. TutorialsPoint - Calculate Salaries | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen