Table Salaries:
+---------------+---------+ | 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.
Example 1:
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.
Problem Overview: You are given salary-related records stored in relational tables and must compute the final salary values for employees using SQL. The task typically requires aggregating salary components, applying conditional logic, and returning the computed salary per employee.
Approach 1: Aggregation with GROUP BY (O(n) time, O(1) extra space)
The core idea is to compute the final salary using SQL aggregation. Each employee may have multiple salary-related records (base salary, adjustments, or entries across time). Use GROUP BY employee_id to combine rows belonging to the same employee and apply aggregation functions such as SUM() to calculate the total salary. If conditional rules are involved, add a CASE WHEN expression to selectively include or transform values before aggregation.
The query typically scans the table once, groups rows by employee, and computes the final salary column in the same step. MySQL performs grouping using internal hashing or sorting depending on the execution plan, giving an effective time complexity of O(n) where n is the number of salary records. Space complexity is O(1) beyond the result set because the database engine handles grouping internally.
This pattern is common in SQL and database interview questions. The key insight is that salary calculation should happen inside the aggregation expression rather than through multiple queries. This keeps the query compact and efficient.
If the problem requires filtering employees after computing salaries (for example, keeping only those above or below a threshold), attach a HAVING clause after the GROUP BY. HAVING works on aggregated results and is frequently used in salary analytics queries. Understanding the difference between WHERE and HAVING is essential when solving aggregation problems in MySQL.
Recommended for interviews: The aggregation approach using GROUP BY is the expected solution. It demonstrates that you understand relational data processing and can compute derived values directly in SQL. A naive approach using multiple subqueries or repeated scans works but is less efficient. Interviewers usually look for a clean single-pass aggregation query that calculates the salary and returns the result set in one statement.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Nested Subqueries | O(n^2) | O(1) | Small datasets or quick prototypes where readability matters more than performance |
| Aggregation with GROUP BY | O(n) | O(1) | Best general solution for computing totals per employee |
| Aggregation with CASE Expressions | O(n) | O(1) | When salary components must be conditionally included or transformed |
LeetCode 1468: Calculate Salaries [SQL] • Frederik Müller • 2,212 views views
Practice Calculate Salaries with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor