Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

Write a SQL query to get the employees with the same salary as the median salary of…

  1. 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);
    
  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:

    1. 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.
    2. 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)).
    3. 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 companies table to get the company name as well.

    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.