Your warehouse has no median function. Write SQL returning the median trade size per symbol from trades(symbol, size), give the exact value for an even row count, and compare the cost against computing the mean.

Your warehouse has no median function. Write SQL returning the median trade size per symbol from trades(symbol, size), give the exact value for an even row count, and compare the cost against computing the mean.

Approach: Rank the rows inside each symbol and count them in the same pass, then select the middle position or the two central positions and average whatever survives.

Number the rows and take the middle: SELECT symbol, avg(size) AS median FROM (SELECT symbol, size, row_number() OVER (PARTITION BY symbol ORDER BY size) AS rn, count(*) OVER (PARTITION BY symbol) AS n FROM trades) t WHERE rn IN ((n + 1) / 2, (n + 2) / 2) GROUP BY symbol. Under integer division those two expressions collapse to the same middle position when n is odd and select the two central positions when n is even, and the outer avg then returns their mean, which is the standard even case definition. On cost, the mean is a streaming aggregate with O(1) state per symbol in a single pass. The median needs the values ordered, so the engine sorts or spills every row inside each partition, which is O(n log n) with a sort cost that exceeds memory on a heavily traded symbol and falls to disk. That is why a production job stores an approximate median from a t-digest or a fixed bucket histogram and reserves the exact query for a small slice, and why percentile_cont, where it exists, carries the same sort cost despite being one line.

Follow-up: How would you compute a rolling 30 day median per symbol without sorting the full history for every day?

Key concepts: row_number, count over partition, even and odd cases, sort cost.