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.
Overview: 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.
Using the Orders table, write a single SQL query that returns, for every order: (1) order_id, user_id, order_date, city, order_value; (2) the previous_order_value for the same user based on order_date (NULL if it is the user's first order); (3) the rank of order_value within its city from highest to lowest; and (4) the percent_rank of order_value within its city.
Tables
Orders(order_id INTEGER, user_id INTEGER, order_date DATE, city VARCHAR(50), order_value DECIMAL(10,2))
Hints
- Use LAG(order_value) OVER (PARTITION BY user_id ORDER BY order_date, order_id) to retrieve the previous order value for each user.
- Use RANK() and PERCENT_RANK() OVER (PARTITION BY city ORDER BY order_value DESC) to compute within-city rankings.