Median delivery fee by city

Hard

Question

A food delivery app wants the MEDIAN delivery fee of each city. The median is the middle value when the fees of the city are sorted. If the city has an even number of deliveries, the median is the average of the two middle values.

For example, the fees 10, 20, 30 and 50 have the median (20 + 30) / 2 = 25.

Return city and median_fee. The order of the rows does not matter.

median
window function
row number
count over
group by

Read first: Lesson 48, SQL patterns that interviews repeat

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

deliveries3 columns · 14 rowsnot run yet

CREATE TABLE deliveries (id INTEGER PRIMARY KEY, city TEXT NOT NULL, fee INTEGER NOT NULL);

Example rows of the deliveries table
id	city	fee
1	Mumbai	30
2	Mumbai	40
3	Mumbai	50
4	Mumbai	100
5	Mumbai	35
6	Delhi	20
7	Delhi	60
8	Delhi	25
9	Delhi	46
10	Pune	40
11	Pune	40
12	Pune	40
13	Pune	90
14	Chennai	55

About this SQL

SQLite has no MEDIAN() and no PERCENTILE_CONT(0.5) WITHIN GROUP (Postgres and Oracle have them). The portable way is ROW_NUMBER and COUNT as window functions, then AVG of the one or two middle rows. In SQLite, whole number / whole number is a whole number. That helps here: (cnt + 1) / 2 and (cnt + 2) / 2 give the same row for an odd count, and the two middle rows for an even count. But the same rule cuts the decimals of an average you divide yourself.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready