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

The standard SQL approach to find the median salary uses the PERCENTILE_CONT window function. This function calculates a percentile based on a continuous distribution, interpolating values when necessary.

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM
    employees;

Mechanics and Why

The median represents the 50th percentile, dividing a dataset into two equal halves. PERCENTILE_CONT(0.5) calculates this by first sorting all salaries. If the total count of employees is odd, it picks the exact middle value. If the count is even, it interpolates by averaging the two middle values, providing a continuous median. The WITHIN GROUP (ORDER BY salary) clause is crucial as it dictates the sort order for the percentile calculation. This operation inherently requires a full sort of the entire dataset.

Key Trade-offs and Scalability

For extremely large datasets, the full sort required by exact percentile functions can be a significant performance bottleneck, especially in distributed systems where it often involves substantial data shuffling (e.g., Spark shuffle) or movement across nodes. To address this, many modern data warehouses and processing engines offer approximate percentile functions. For instance, BigQuery and Snowflake provide APPROX_PERCENTILE, while Spark SQL also has APPROX_PERCENTILE. These functions typically use sampling or sketch algorithms, trading a small, configurable degree of precision for substantial performance improvements. This makes them highly suitable for analytical use cases where an exact median isn't strictly required, such as dashboarding, trend analysis, or anomaly detection on massive datasets.

In the interview, also mention: Consider how NULL values in the salary column should be handled (e.g., WHERE salary IS NOT NULL) and the potential impact of data skew on performance for exact calculations.