Median Score by Department — Solution & ExplanationProblems / Median Score by Department / EditorialApproachDuckDB ShortcutDuckDB provides MEDIAN(score) as a built-in aggregate — equivalent to PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score).Manual Approach (Window Functions)When MEDIAN isn't available, compute it from first principles:

Rank: ROW_NUMBER() OVER (PARTITION BY department ORDER BY score) assigns positions 1..n.

Count: COUNT(*) OVER (PARTITION BY department) gives n.

Middle positions: For n values, the median uses positions FLOOR((n+1)/2) and CEIL((n+1)/2). For odd n, both are the same.

Average: Filter to the middle row(s) and take AVG(score).

ExampleFor scores [68, 75, 82, 90] (n=4):

Positions: 1, 2, 3, 4

FLOOR(5/2)=2, CEIL(5/2)=3

Median = AVG(75, 82) = 78.5

Complexity

MEDIAN(): O(n) per group (selection algorithm)

Manual: O(n log n) per group (sort for ROW_NUMBER)

Hints

DuckDB has a built-in MEDIAN() aggregate function that handles both odd and even counts.

For the manual approach: rank scores with ROW_NUMBER, find the middle position(s), then average them.

For even count n, the median uses positions FLOOR((n+1)/2) and CEIL((n+1)/2). For odd n, both evaluate to the same position.

Solve Median Score by Department yourself →