# 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.

## 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

Rating: 1 out of 5.

Posted in SQL