Restaurant Growth
Door Dash
Problem Statement
As a data analyst at DoorDash, you are tracking the growth of a local restaurant. You want to analyze the spending patterns using a 7-day moving window.
Write an SQL query to calculate the moving average of how much the customer paid in a seven-day window (current day + 6 days before).
Rules:
- The window must include the current day and the previous 6 days.
- The average_amount should be rounded to 2 decimal places.
- The first 6 days of the restaurant's history should be excluded because they do not have a full 7-day window.
- The result columns should be visited_on, amount (total for 7 days), and average_amount.
- Results must be sorted by visited_on in ascending order.
Tests your understanding of
Basic SQL, Window Functions, Moving Average and Aggregation
Input Tables
customer
| customer_id(INTEGER) | name(VARCHAR) | visited_on(DATE) | amount(INTEGER) |
|---|---|---|---|
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
Expected Output
| visited_on(DATE) | amount(INTEGER) | average_amount(DECIMAL) |
|---|---|---|
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
Tags
MediumBasic SQLWindow FunctionsMoving AverageAggregation
20-25 min
51%