Company: Stripe | Difficulty: medium
Stripe's analytics team wants to understand the typical payment size across regions.
For each region, calculate median_amount from its non-NULL amount values. Exclude transactions where amount is NULL. Exclude a region entirely when it has no non-NULL amount values. The median must handle both odd and even counts: use the middle value for an odd count, or the average of the two middle values for an even count.
region
median_amount
NULL
amount
Return region and median_amount, in that order. Round median_amount to exactly 2 decimal places. Do not use built-in median or percentile functions.
2
Sort by the rounded median_amount descending, then region ascending.
transactions
transaction_id
INTEGER PRIMARY KEY
VARCHAR(50) NOT NULL
DECIMAL(10,2)
1
North
100.00
200.00
3
300.00
4
South
50.00
5
150.00
6
250.00
7
350.00
8
East
80.00
9
120.00
10
North has three valid amounts, so its median is 200.00. South has four valid amounts, so its median is the average of 150.00 and 250.00, which is 200.00. East has two valid amounts, so its median is 100.00. North appears before South because tied medians are sorted by region ascending.
DROP TABLE IF EXISTS transactions; CREATE TABLE transactions ( transaction_id INT PRIMARY KEY, region VARCHAR(50) NOT NULL, amount DECIMAL(10,2) ); INSERT INTO transactions VALUES (1, 'North', 100.00), (2, 'North', 200.00), (3, 'North', 300.00), (4, 'South', 50.00), (5, 'South', 150.00), (6, 'South', 250.00), (7, 'South', 350.00), (8, 'East', 80.00), (9, 'East', 120.00), (10, 'East', NULL);
← All SQL Challenges · Stripe Questions · Learn SQL Free