Restaurant Growth II
Door Dash
Problem Statement
You are analyzing customer spending habits for DoorDash. Write an SQL query to calculate the moving average of the total amount spent by customers over a 14-day window (current day + 13 preceding days).
Rules:
- The moving average should be rounded to 2 decimal places.
- You should only include dates that have at least 13 days of prior data (i.e., the first 13 days of the dataset should be excluded from the final output).
- Output columns: visited_on, amount (sum of 14 days), average_amount (14-day average).
- Results must be sorted by visited_on in ascending order.
Tests your understanding of
Window Functions, Date Arithmetic and Aggregation
Input Tables
Customer_Orders
| customer_id(INTEGER) | visited_on(DATE) | amount(INTEGER) |
|---|---|---|
| 1 | 2026-01-01 | 100 |
| 2 | 2026-01-02 | 110 |
| 3 | 2026-01-14 | 120 |
| 4 | 2026-01-15 | 130 |
Restaurants
| restaurant_id(INTEGER) | name(VARCHAR) |
|---|---|
| 101 | Burger King |
Promotions
| promo_id(INTEGER) | discount_pct(INTEGER) |
|---|---|
| 1 | 10 |
Expected Output
| visited_on(DATE) | amount(INTEGER) | average_amount(DECIMAL) |
|---|---|---|
| 2026-01-14 | 330 | 23.57 |
| 2026-01-15 | 460 | 32.86 |
Tags
MediumWindow FunctionsDate ArithmeticAggregation
25-30 min
38%