Write a query to calculate the median salary of employees in a table.

Company: BlackRock

CTC: 26LPA

Source: Linked Post

Q: Write a query to calculate the median salary of employees in a table.

Solution:

SELECT AVG(salary) AS median_salary
FROM (
    SELECT salary
    FROM employees
    ORDER BY salary
    LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)
) subquery;

Explanation:

Here we need to find out the median salary from the employees table. To calculate the median salary, we need some essential information, like the total number of employees in the table.

First, let’s understand what a median is. Median is the middle value in a sorted list of values.

• If there are an even number of employees (e.g., 10), the median will be the average of the 5th and 6th salaries in the sorted list.
• If there are an odd number of employees (e.g., 9), the median will be the salary in the 5th position.

Steps to Calculate the Median:

1. Sorting: First, we sort the salaries in ascending order.
2. Identifying Middle Rows:
   • If the number of employees is odd, we select the exact middle row.
   • If the number of employees is even, we select the two middle rows.
3. Average Calculation: If there’s one salary (odd count), the median is that salary. If there are two salaries (even count), the median is the average of those two.

How the Query Works:

To achieve this, we divide the logic into steps:

1. Subquery to Count Rows:
   • We count all the rows in the employees table using COUNT(*).
   • This tells us the total number of employees, which helps us decide if the count is odd or even.
2. Determine Limit:
   • Using the formula 2 - (COUNT(*) % 2), we decide how many rows to fetch:
     • If the count is odd (COUNT % 2 = 1), 2 - 1 = 1 → Select 1 row.
     • If the count is even (COUNT % 2 = 0), 2 - 0 = 2 → Select 2 rows.
3. Determine Offset:
   • The formula (COUNT(*) - 1) / 2 calculates how many rows to skip (offset) before reaching the middle.
     • For an odd count (e.g., 9), (9 - 1) / 2 = 4 → Start at the 5th row.
     • For an even count (e.g., 10), (10 - 1) / 2 = 4 → Start at the 5th row.
4. Sort and Fetch Middle Rows:
   • We sort the salaries in ascending order and apply the LIMIT and OFFSET based on the results of the calculations.
   • For an odd count, only the middle row is fetched.
   • For an even count, the two middle rows are fetched.
5. Calculate the Median:
   • Finally, we use AVG(salary) in the outer query to compute the average of the rows returned by the subquery.
   • If there’s only one row (odd count), the average is the same as that salary.
   • If there are two rows (even count), the average is their median.

Order of Execution:

1. Count all rows (COUNT(*)).
2. Calculate offset to skip rows before the middle.
3. Sort salaries in ascending order.
4. Apply offset and limit to fetch the middle row(s).
5. Compute the average of the fetched rows to get the median salary.

Summary:

• If there’s an odd number of employees, one row is fetched, and the median is that salary.
• If there’s an even number of employees, two rows are fetched, and the average of those salaries is the median.