Stripe's analytics team wants to understand the typical payment size across different regions. Calculate the median transaction amount for each region. Ignore transactions where the amount is missing. The median must work correctly whether a region has an odd or even number of valid transactions. Return the region and its median transaction amount rounded to two decimal places. Sort the results by median transaction amount from highest to lowest. If two regions have the same median, sort those region names in descending alphabetical order. Do not use built-in median or percentile functions. ### Table Schema | Table | Column | Type | |---|---|---| | transactions | transaction_id | integer | | transactions | region | varchar(50) | | transactions | amount | decimal(10,2) | ### Example Input: transactions | transaction_id | region | amount | |---:|---|---:| | 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 | ### Example Output Explanation North has three valid amounts, so its middle value 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 the average of 80.00 and 120.00, which is 100.00. The missing East amount is excluded.
DROP TABLE IF EXISTS transactions; CREATE TABLE transactions ( transaction_id INT, region VARCHAR(50), 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);