Drill 2 of 8 in Fintech questions
0

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…