Objective

Write a SQL query to calculate the median number of searches made by users. The table is a frequency summary where each row contains a search count and the number of users who performed that many searches. Round the result to 1 decimal place.

Table Schema:

• search_frequency

Task Requirements:

• Each row represents multiple users, not a single user
• Expand the frequency data so each user is represented individually before computing the median
• Use continuous median interpolation (average the two middle values for even-sized datasets)
• Round the result to 1 decimal place
  
  

Output columns: median_searches

WITH expanded AS (
    SELECT searches
    FROM search_frequency
    CROSS JOIN GENERATE_SERIES(1, num_users)
)
SELECT
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY searches)::DECIMAL, 1) AS median_searches
FROM expanded;

Explanation

Step 1: Expand the frequency table

WITH expanded AS (
    SELECT searches
    FROM search_frequency
    CROSS JOIN GENERATE_SERIES(1, num_users)
)

The frequency table is compressed: each row represents many users. CROSS JOIN GENERATE_SERIES(1, num_users) repeats each searches value num_users times, producing one row per user. For example, a row with searches = 3 and num_users = 5 becomes five rows each containing the value 3.

Step 2: Calculate the median

SELECT
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY searches)::DECIMAL, 1) AS median_searches
FROM expanded;

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY searches) computes the continuous 50th percentile (median) of the expanded dataset. For even-sized datasets, it interpolates by averaging the two middle values. The ::DECIMAL cast converts the result from double precision to DECIMAL, allowing ROUND(..., 1) to limit the output to 1 decimal place.