Compare with the previous order
hardCompare with the previous order
Best Buy SQL Interview Question
Best Buy's analytics team wants to see how each online order compares with the one placed just before it.
For every order, show the order_id, the amount, and the amount of the order placed just before it as previous_amount. Use all orders, whatever their status. The first order has no earlier order, so its previous_amount is NULL. Sort the rows by order date, earliest first.
Asked of
- Product Analyst
- Data Scientist
- Analytics Engineer
- Data Engineer
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
Example Output
| order_id | amount | previous_amount |
|---|---|---|
| 1001 | 120.5 | NULL |
| 1002 | 2400 | 120.5 |
| 1003 | 89.99 | 2400 |
| 1004 | 45 | 89.99 |
| 1005 | 610.75 | 45 |
| 1006 | 1850.25 | 610.75 |
| 1007 | 3200 | 1850.25 |
| 1008 | 75 | 3200 |
| 1009 | 55.4 | 75 |
| 1010 | 132.6 | 55.4 |
Explanation
Order 1002 was placed right after order 1001, so its previous amount is 120.50. Order 1001 is the earliest order, so it has no previous amount.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Best Buy
Difficulty
hard
Topic
window functions
Your query
Starting the SQL engine...