Interview Question Bank · PERCENTILE_CONT · JOIN · ROUND
2. Median purchase by customer segment
EasyFintechQuestion #99
Concept: PERCENTILE_CONT · JOIN · ROUNDshow
Card spend is skewed: a few big tickets drag the average far above a typical purchase, so the median is the sturdier 'typical' figure. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) returns it per group, averaging the two middle values when a group has an even count. It returns double precision, so cast to numeric before ROUND(x, 2).
Example (different data):
SELECT d.region, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY r.fare))::numeric, 2) AS median_fare FROM rides r JOIN drivers d ON d.id = r.driver_id GROUP BY d.region;
Your task
Tallyfin's card team wants a 'typical purchase' figure for each customer segment, and the averages keep getting skewed by a few very large tickets.
Join fin_purchases to fin_customers. For each segment, return segment, purchases (number of purchases) and median_amount (the median purchase amount, ROUND(...::numeric, 2)). Sort by segment.
Starting Postgres in your browser…
Checking your access…