Coding questions · DoorDash

Analyze Customer Purchase Patterns Using SQL Query

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.

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:

order_id, customer_id, order_value, order_date;
the previous order_value for the same customer (lag);
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.