Analyze Customer Purchase Patterns Using SQL Query
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
orders
+----------+-------------+-------------+------------+------+
| order_id | customer_id | order_value | order_date | city |
+----------+-------------+-------------+------------+------+
| 101 | 1 | 120.50 | 2023-07-01 | LA |
| 102 | 1 | 80.00 | 2023-07-18 | LA |
| 103 | 2 | 200.00 | 2023-07-02 | NY |
| 104 | 1 | 75.00 | 2023-08-01 | LA |
| 105 | 3 | 50.00 | 2023-07-05 | SF |
+----------+-------------+-------------+------------+------+
##### Scenario
E-commerce platform wants detailed insights into customer purchasing patterns over time.
##### Question
Write a SQL query that, for every order, returns:
1) order_id, customer_id, order_value, order_date;
2) the previous order_value for the same customer (lag);
3) the rank and percent_rank of the current order_value among that customer’s lifetime orders, ordered by order_date descending.
##### Hints
Use LAG, RANK, PERCENT_RANK window functions partitioned by customer_id and ordered by order_date.
Overview: This question evaluates a candidate's competence with SQL data manipulation and windowing concepts, specifically using ordering and partitioning to compare and rank events within a customer's purchase history.
For every order, return order_id, customer_id, order_value, order_date; the previous order_value for the same customer; and the rank and percent_rank of the order by order_date descending within each customer.
Tables
orders(order_id INTEGER, customer_id INTEGER, order_value DECIMAL(10,2), order_date DATE, city VARCHAR(2))
Hints
- Use LAG(order_value) OVER (PARTITION BY customer_id ORDER BY order_date DESC) to get the previous order_value per customer.
- Use RANK() and PERCENT_RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) for recency ranking within each customer.