Restaurant Growth - Problem

You are given a table Customer that contains data about customer transactions in a restaurant. Each row represents a customer visit with the amount they paid.

Problem: Calculate the 7-day moving average of total daily revenue. For each day, compute the average of revenue from that day plus the previous 6 days (7 days total).

Requirements:

  • Only include dates that have at least 7 days of data (including the current day)
  • Round the moving average to 2 decimal places
  • Order results by visited_on in ascending order

Table Schema:

Customer
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+

Primary Key: (customer_id, visited_on)

Table Schema

Customer
Column Name Type Description
customer_id PK int Unique identifier for each customer
name varchar Customer name
visited_on PK date Date when customer visited the restaurant
amount int Total amount paid by customer on that visit
Primary Key: (customer_id, visited_on)
Note: A customer can visit multiple times, but only once per day. Multiple customers can visit on the same day.

Input & Output

Example 1 — Basic 7-Day Moving Average
Input Table:
customer_id name visited_on amount
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
Output:
visited_on amount average_amount
2019-01-07 860 122.86
2019-01-08 840 120
2019-01-09 840 120
2019-01-10 1000 142.86
💡 Note:

The first result appears on 2019-01-07 because it's the first date with 7 days of data. For 2019-01-07: sum of amounts from Jan 1-7 is 860, and average is 860/7 = 122.86. The window slides forward each day, maintaining exactly 7 days in the calculation.
Example 2 — Multiple Customers Same Day
Input Table:
customer_id name visited_on amount
1 Alice 2019-02-01 50
2 Bob 2019-02-01 75
3 Carol 2019-02-02 100
1 Alice 2019-02-03 80
4 Dave 2019-02-04 90
5 Eve 2019-02-05 60
2 Bob 2019-02-06 70
6 Frank 2019-02-07 85
Output:
visited_on amount average_amount
2019-02-07 610 87.14
💡 Note:

Multiple customers can visit on the same day. The daily amounts are summed first: Feb 1 total = 125 (50+75), Feb 2 = 100, etc. The 7-day moving average for Feb 7 includes the sum of all daily totals from Feb 1-7: (125+100+80+90+60+70+85)/7 = 87.14.

Constraints

  • 1 ≤ customer_id ≤ 1000
  • name contains only English letters and spaces
  • visited_on is a valid date
  • 1 ≤ amount ≤ 1000
  • There is at least one customer visit every day in the input range

Visualization

Tap to expand
Restaurant Growth - 7-Day Moving Average INPUT DATA date amount 2019-01-01 100 2019-01-02 110 2019-01-03 120 2019-01-04 130 2019-01-05 110 2019-01-06 140 2019-01-07 150 2019-01-08 80 2019-01-09 70 ... more rows Customer Table with visited_on and amount ALGORITHM STEPS 1 Self Join Table Join dates within 6 days using DATEDIFF 2 Group By Date Aggregate 7 days of data per output row 3 Calculate Average SUM(amount)/7 ROUND to 2 decimals 4 Filter Results HAVING COUNT = 7 ORDER BY date ASC Window: [day-6 ... day] d-6 d-5 d-4 d-3 d-2 d-1 d 7 consecutive days FINAL RESULT date avg_amount 2019-01-07 122.86 2019-01-08 120.00 2019-01-09 114.29 2019-01-10 111.43 ... continues OK - Success! 7-day moving average Revenue Trend Key Insight: Self-join approach: Join each date with all dates in its 7-day window using DATEDIFF(a.date, b.date) BETWEEN 0 AND 6. Use HAVING COUNT(DISTINCT b.date) = 7 to ensure full 7 days of data. TutorialsPoint - Restaurant Growth | Optimal Solution

Related Problems

Asked in
Amazon 12 Google 8 Microsoft 6 Facebook 5
28.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen