SQL Interview Questions – Calculate Salaries

Problem Description –

Write an SQL query to 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 query result format is in the following example.

Difficulty Level – Medium

Problem Link – Calculate Salaries

Solution –

SELECT 
    t1.company_id,
    t1.employee_id,
    t1.employee_name,
    ROUND(CASE WHEN t2.max_sal >= 1000 AND t2.max_sal <= 10000 then salary * 0.76
        WHEN t2.max_sal > 10000 THEN salary * 0.51 
        Else salary end, 0) as salary
FROM Salaries as t1 JOIN (SELECT company_id, MAX(salary) as max_sal FROM Salaries GROUP BY 1) as t2
ON t1.company_id = t2.company_id