Data Analyst Interview
Menu
Browse in your language. All mock interviews, preparation sessions and feedback are in English only.

How to Calculate a Median by Group in SQL

To calculate a median by group, sort the nonmissing observations, number them, count the observations in each group, and average the one or two middle rows. Keep a separate coverage count so that a group with no measured values remains visible. This worked SQLite example handles both odd and even samples without a percentile extension.

What does the example measure?

A fictional support team compares resolved tickets for US, UK, and German customers. US elapsed resolution times are 1, 2, 8, and 30 hours. UK times are 2, 2, and 9 hours, with a fourth ticket missing its duration. Germany has one ticket with a missing duration. Each row is one unique resolved ticket; a missing duration means unknown, not zero.

The US median is (2 + 8) / 2 = 5 hours. The UK median is 2 hours across three measured tickets. Germany has no measurable median. These invented records are for interview practice, not country benchmarks. For choosing which summary to present, see the mean-versus-median support dashboard case.

Run the grouped median query

This self-contained query uses SQLite window functions. The middle-position expressions rely on SQLite integer division because n is an integer. When adapting it to another database, check division behavior and use explicit integer rounding if required.

WITH tickets(market, ticket_id, hours) AS (
  VALUES ('US',1,1.0), ('US',2,2.0),
         ('US',3,8.0), ('US',4,30.0),
         ('UK',5,2.0), ('UK',6,2.0),
         ('UK',7,9.0), ('UK',8,NULL),
         ('DE',9,NULL)
), ranked AS (
  SELECT market, hours,
    ROW_NUMBER() OVER (
      PARTITION BY market
      ORDER BY hours, ticket_id
    ) AS position,
    COUNT(*) OVER (PARTITION BY market) AS n
  FROM tickets
  WHERE hours IS NOT NULL
), medians AS (
  SELECT market, AVG(hours) AS median_hours
  FROM ranked
  WHERE position IN ((n + 1) / 2, (n + 2) / 2)
  GROUP BY market
), coverage AS (
  SELECT market, COUNT(*) AS tickets_total,
         COUNT(hours) AS tickets_measured
  FROM tickets
  GROUP BY market
)
SELECT c.market, c.tickets_total,
       c.tickets_measured, m.median_hours
FROM coverage c
LEFT JOIN medians m ON m.market = c.market
ORDER BY c.market;

Read the result with coverage

The verified output is DE: 1 total ticket, 0 measured, NULL median; UK: 4 total, 3 measured, 2.0 hours; US: 4 total, 4 measured, 5.0 hours. The left join retains Germany even though filtering missing durations removes it from the ranked observations.

Publish the measured count alongside the median. A UK median of 2 hours describes the three observed durations; the missing fourth duration could change the complete four-ticket median. It is not evidence that all four tickets had a typical duration of 2 hours.

Why do the two position formulas work?

With four observations, integer division makes (n + 1) / 2 equal 2 and (n + 2) / 2 equal 3. Averaging positions 2 and 3 produces the even-sample median. With three observations, both expressions equal 2. IN selects that row once, so averaging returns the middle value.

ROW_NUMBER gives every observation its own position, including tied durations. The ticket ID makes ordering deterministic within a tie. Do not replace it with RANK or DENSE_RANK: those functions count positions differently when values repeat. Repeated durations are valid observations and should not be removed with DISTINCT.

COUNT(*) OVER (PARTITION BY market) supplies the full measured sample size to every row. Adding ORDER BY hours to that count can turn it into a cumulative window count under the default frame, invalidating the middle-position calculation. SQLite’s official window-function documentation explains row numbering, partitions, and frames.

What should you check before using real ticket data?

Validate unique ticket IDs and numeric, nonnegative durations before ranking. Repeated ticket events must first be reduced to the intended ticket-level record; repeated duration values alone are not duplicates. Keep a separate rejected-record count if invalid durations are excluded.

Choose the cohort explicitly. This example summarizes resolved tickets, so open backlog is outside the calculation. Decide whether production reporting includes tickets created during the period or tickets resolved during the period. For New York, London, and Berlin operations, calculate elapsed duration from correctly interpreted timestamps; business-hour duration needs the relevant work calendar.

An entirely absent market needs a market roster as the left side of the final join. The coverage CTE here retains only markets present in the input, including those with all-null durations. Also test a single measured ticket, two measured tickets, tied values, and an empty input before adapting the query.

How would you explain it in an interview?

“I define the ticket population and remove unknown durations only from the median calculation. I assign row positions and a full group count, average the middle positions, and join the result to coverage counts. I report missingness and open backlog separately before comparing markets.”

Practice the related NULL-versus-zero reporting distinction and the coverage-aware inventory summary. The same reporting habit applies: an unavailable measurement should remain visible.

Leave a Comment

✉ WhatsApp