Analyze Spending Patterns and Restaurant Performance Using SQL/Python
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
orders
+-------------+---------+---------------+---------------------+
| delivery_id | user_id | restaurant_id | order_date |
+-------------+---------+---------------+---------------------+
| 1001 | 501 | 3001 | 2025-07-12 18:45:00 |
| 1002 | 502 | 3002 | 2025-07-12 19:02:00 |
| 1003 | 501 | 3003 | 2025-07-13 12:15:00 |
| 1004 | 503 | 3001 | 2025-07-13 13:20:00 |
| 1005 | 504 | 3004 | 2025-07-13 14:05:00 |
+-------------+---------+---------------+---------------------+
values
+-------------+----------+
| delivery_id | subtotal |
+-------------+----------+
| 1001 | 24.99 |
| 1002 | 18.75 |
| 1003 | 45.00 |
| 1004 | 30.50 |
| 1005 | 27.25 |
+-------------+----------+
##### Scenario
Food-delivery platform analytics: understand spending patterns and restaurant performance using the orders table.
##### Question
Retrieve the list of users who placed more than 30 deliveries for each month.
Excluding those users from previous question, identify the single highest-spending user for each month.
For restaurant_id = 25, compute the month-over-month change in total revenue.
Determine the proportion of orders placed at restaurants that fall in the bottom 25% of restaurants by total revenue.
##### Hints
Use window functions, aggregation by YEAR-MONTH, CTEs, and percentile logic.
Quick Answer: This question evaluates SQL and Python data-manipulation skills including time-based aggregation, window functions, percentile-based segmentation, and revenue attribution to identify frequent and high-spending users and assess restaurant performance.