Drill 8 of 10 in Banking questions
0

Interview Question Bank · PERCENTILE_CONT · CTE · net totals

8. Whale cardholders: 3x the median

HardBankingQuestion #271

Concept: PERCENTILE_CONT · CTE · net totalsshow

Outlier rules like 'at least 3x the typical customer' should use the median, not the mean, because the outliers themselves drag the mean up. Aggregate to one row per customer first, then take PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) over those rows, and CROSS JOIN that single value back to compare. Net amounts (purchases minus refunds) come from summing signed amounts, so don't filter out the negatives.

Example (different data):

WITH per_driver AS (
  SELECT driver_id, SUM(km) AS km FROM trips GROUP BY driver_id
), m AS (
  SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY km) AS median_km FROM per_driver
)
SELECT d.driver_id, d.km
FROM per_driver d CROSS JOIN m
WHERE d.km >= 2 * m.median_km;

Your task

Larkspur Bank's card team calls a cardholder a whale when their net spend in a quarter is at least 3 times the median cardholder's net spend that quarter. Refunds arrive as negative amounts.

For Q1 2025 (January 1 to March 31 inclusive), compute each cardholder's net spend (purchases minus refunds), counting only cardholders with a Q1 transaction. Return the whales: name, q1_spend and times_median (q1_spend divided by the median of those cardholders' Q1 spend, rounded to 2). Sort by q1_spend descending, then name.

Starting Postgres in your browser…

Checking your access…