gpt-4o-mini-2024-07-18· OpenAI
WildChat-4.8M (AI2)
Write a SQL query to get the employees with the same salary as the median salary of…
- User
Write a SQL query to get the employees with the same salary as the median salary of their company.
Dataset: CREATE TABLE companies ( company_id INT PRIMARY KEY, company_name VARCHAR(100) );
CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), company_id INT, salary DECIMAL(10, 2), FOREIGN KEY (company_id) REFERENCES companies(company_id) ); INSERT INTO companies (company_id, company_name) VALUES (1, 'TechCorp'), (2, 'HealthInc'), (3, 'FinanceSolutions'), (4, 'EduGlobal'), (5, 'RetailWorld'); INSERT INTO employees (employee_id, employee_name, company_id, salary) VALUES (1, 'Alice', 90000, 1), (2, 'Bob', 70000, 2), (3, 'Charlie', 80000, 1), (4, 'David', 95000, 3), (5, 'Eva', 65000, 4), (6, 'Frank', 60000, 5), (7, 'Grace', 72000, 2); - Assistant
To find the employees who have the same salary as the median salary of their respective companies, you can use the following SQL query. This uses common table expressions (CTEs) to calculate the median salary for each company and then joins this back to the employees table to find those employees with matching salaries.
Here’s the SQL query:
WITH RankedSalaries AS ( SELECT company_id, salary, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY salary) AS rn, COUNT(*) OVER (PARTITION BY company_id) AS cnt FROM employees ), MedianSalaries AS ( SELECT company_id, CASE WHEN cnt % 2 = 1 THEN MAX(salary) -- For odd count, take the middle value ELSE AVG(salary) -- For even count, take the average of the two middle values END AS median_salary FROM RankedSalaries GROUP BY company_id, cnt ) SELECT e.employee_id, e.employee_name, e.salary, c.company_name FROM employees e JOIN MedianSalaries ms ON e.company_id = ms.company_id JOIN companies c ON e.company_id = c.company_id WHERE e.salary = ms.median_salary;Explanation:
-
RankedSalaries CTE:
- Calculates a rank for each salary within each company using
ROW_NUMBER()and also counts the number of employees (COUNT(*)) in each company.
- Calculates a rank for each salary within each company using
-
MedianSalaries CTE:
- Computes the median salary for each company depending on whether the count of salaries is odd or even.
- If it's odd, it selects the middle value (
MAX(salary)for the middle rank). - If it's even, it computes the average of the two middle values (
AVG(salary)).
-
Final Selection:
- This part of the query selects the employee details where the employee's salary matches the median salary calculated in the previous CTEs, joining with the
companiestable to get the company name as well.
- This part of the query selects the employee details where the employee's salary matches the median salary calculated in the previous CTEs, joining with the
Note:
- This query assumes that you are executing it in an environment that supports common table expressions (like PostgreSQL, SQL Server, etc.). If you're using MySQL, ensure you are on version 8.0 or higher as CTEs are available from MySQL 8 onwards.
-