Interview Question Bank · Running SUM · weighted median · zero counts
10. Median from a frequency table
HardGeneralQuestion #202
Concept: Running SUM · weighted median · zero countsshow
A frequency table stores each value once with a count, so a plain median over its rows ignores the counts. Picture the values lined up count times each: with a running total of the counts, the middle value is the one whose span covers position total / 2, which is where running >= total / 2.0 and running - count <= total / 2.0. With an even total that can match two values; average them. Drop zero counts first, since their empty span can sit right on the midpoint.
Example (different data):
SELECT score, students, SUM(students) OVER (ORDER BY score) AS running, SUM(students) OVER () AS total FROM score_counts WHERE students > 0;
Your task
Cobble Lane's three grocery stores only keep a summary of basket sizes: how many baskets had 1 item, 2 items and so on. The operations lead wants each store's median basket size.
Return store, total_baskets and median_items (the median number of items per basket, counting each basket once, rounded to 1 decimal). When a store has an even number of baskets, the median is the average of the two middle baskets. Sort by store.
Starting Postgres in your browser…
Checking your access…