Problem

Table Salaries:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| company_id    | int     |
| employee_id   | int     |
| employee_name | varchar |
| salary        | int     |
+---------------+---------+
In SQL,(company_id, employee_id) is the primary key for this table.
This table contains the company id, the id, the name, and the salary for an employee.

Find the salaries of the employees after applying taxes. Round the salary to the nearest integer.

The tax rate is calculated for each company based on the following criteria:

  • 0% If the max salary of any employee in the company is less than $1000.
  • 24% If the max salary of any employee in the company is in the range [1000, 10000] inclusive.
  • 49% If the max salary of any employee in the company is greater than $10000.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

    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    |
    | 3          | 7           | Bocaben       | 100    |
    | 3          | 2           | Ognjen        | 2200   |
    | 3          | 13          | Nyancat       | 3300   |
    | 3          | 15          | Morninngcat   | 7777   |
    +------------+-------------+---------------+--------+
    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    |
    | 3          | 7           | Bocaben       | 76     |
    | 3          | 2           | Ognjen        | 1672   |
    | 3          | 13          | Nyancat       | 2508   |
    | 3          | 15          | Morninngcat   | 5911   |
    +------------+-------------+---------------+--------+
    Explanation: 
    For company 1, Max salary is 21300. Employees in company 1 have taxes = 49%
    For company 2, Max salary is 700. Employees in company 2 have taxes = 0%
    For company 3, Max salary is 7777. Employees in company 3 have taxes = 24%
    The salary after taxes = salary - (taxes percentage / 100) * salary
    For example, Salary for Morninngcat (3, 15) after taxes = 7777 - 7777 * (24 / 100) = 7777 - 1866.48 = 5910.52, which is rounded to 5911.

Solution

Method 1 – Tax Rate by Company and Salary Calculation

Intuition

We first determine the maximum salary for each company to decide the tax rate. Then, we join this information back to the original table and apply the tax rate to each employee’s salary, rounding the result to the nearest integer.

Approach

  1. For each company, find the maximum salary.
  2. Assign a tax rate based on the maximum salary:
    • 0% if max salary < 1000
    • 24% if 1000 ≤ max salary ≤ 10000
    • 49% if max salary > 10000
  3. Join the tax rate with the original Salaries table.
  4. For each employee, calculate the salary after tax: ROUND(salary * (1 - tax_rate / 100)).
  5. Return company_id, employee_id, employee_name, and the new salary.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

WITH max_salary AS (
  SELECT company_id, MAX(salary) AS max_sal
  FROM Salaries
  GROUP BY company_id
),
tax_rate AS (
  SELECT company_id,
    CASE
      WHEN max_sal < 1000 THEN 0
      WHEN max_sal <= 10000 THEN 24
      ELSE 49
    END AS tax
  FROM max_salary
)
SELECT s.company_id, s.employee_id, s.employee_name,
  ROUND(s.salary * (1 - t.tax / 100)) AS salary
FROM Salaries s
JOIN tax_rate t ON s.company_id = t.company_id;

Complexity

  • ⏰ Time complexity: O(n) — n is the number of employees (single scan and join).
  • 🧺 Space complexity: O(1) — Only accumulators are used.