Analyze Order Spending Patterns Across Cities Using SQL
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Orders
order_id | user_id | order_date | city | order_value
1 | 101 | 2023-01-03 | LA | 23.50
2 | 102 | 2023-01-04 | NY | 45.00
3 | 101 | 2023-01-10 | LA | 19.00
4 | 103 | 2023-01-11 | LA | 55.75
5 | 104 | 2023-01-12 | SF | 32.20
##### Scenario
An e-commerce marketplace wants detailed insight into order spending patterns across cities.
##### Question
Using the Orders table, write one SQL query that returns for every order:
1) order_id, user_id, order_date, city, order_value,
2) previous_order_value of the same user (NULL if first),
3) rank of the order_value within its city ordered from highest to lowest, and
4) percent_rank of the order_value within its city.
##### Hints
Combine LAG(), RANK() and PERCENT_RANK() partitioned appropriately; remember to order windows by order_date or order_value as required.
Quick Answer: This question evaluates proficiency with SQL window functions, time-aware row-level analytics, and the ability to compute intra-group rankings and prior-row comparisons on order data.