Top restaurant in each city
hardTop restaurant in each city
DoorDash SQL Interview Question
DoorDash's partnerships team wants to thank the best-selling restaurant in each city it serves.
For each city, find the restaurant_name with the highest total value of delivered orders, and that total as total_order_value. Each city has one clear top restaurant, so you do not need to handle ties.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Analytics Engineer
- Data Engineer
deliveriesTable12 rows
| Column Name | Type |
|---|---|
| delivery_id | BIGINT |
| restaurant_id | BIGINT |
| dasher_id | BIGINT |
| ordered_at | TIMESTAMP |
| delivered_at | TIMESTAMP |
| order_value | DOUBLE |
| status | VARCHAR |
restaurantsTable6 rows
| Column Name | Type |
|---|---|
| restaurant_id | BIGINT |
| restaurant_name | VARCHAR |
| cuisine | VARCHAR |
| city | VARCHAR |
deliveriesExample Input
| delivery_id | restaurant_id | dasher_id | ordered_at | delivered_at | order_value | status |
|---|---|---|---|---|---|---|
| 7001 | 1 | 51 | 2024-04-12 11:58:00 | 2024-04-12 12:26:00 | 32.4 | delivered |
| 7002 | 2 | 52 | 2024-04-12 12:05:00 | 2024-04-12 12:41:00 | 27.8 | delivered |
| 7003 | 3 | 53 | 2024-04-12 12:20:00 | NULL | 18.5 | canceled |
| 7004 | 4 | 54 | 2024-04-12 18:02:00 | 2024-04-12 18:48:00 | 45.6 | delivered |
| 7005 | 5 | 55 | 2024-04-12 18:15:00 | 2024-04-12 18:37:00 | 22.1 | delivered |
| 7006 | 1 | 52 | 2024-04-12 18:30:00 | 2024-04-12 19:04:00 | 41.25 | delivered |
| 7007 | 6 | 56 | 2024-04-12 19:10:00 | 2024-04-12 19:52:00 | 36.9 | delivered |
restaurantsExample Input
| restaurant_id | restaurant_name | cuisine | city |
|---|---|---|---|
| 1 | Taco Loco | Mexican | San Francisco |
| 2 | Pho Real | Vietnamese | San Francisco |
| 3 | Burger Barn | American | San Francisco |
| 4 | Curry House | Indian | Denver |
| 5 | Nacho Mama | Mexican | Denver |
| 6 | Big Bite Burgers | American | Denver |
Example Output
| city | restaurant_name | total_order_value |
|---|---|---|
| Denver | Curry House | 45.6 |
| San Francisco | Taco Loco | 73.65 |
Explanation
Taco Loco delivered two orders in San Francisco worth 32.40 and 41.25, a total of 73.65, which beats Pho Real's 27.80. In Denver, Curry House leads with a single order worth 45.60. Burger Barn's canceled order does not count.
The example above is a small slice of the data. Your query runs against the full tables.
Company
DoorDash
Difficulty
hard
Topic
window functions
Your query
Starting the SQL engine...