Drill 10 of 12 in General questions
0

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…