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 AverageCustomer Transactionscustomer_idvisited_onamount101-01100201-02110301-03120... more transactions ...701-07150GROUP BY+ WINDOW7-DAY SLIDING WINDOWSUM() OVER(ROWS BETWEEN6 PRECEDING ANDCURRENT ROW)MOVINGAVERAGEGrowth Analysis Resultsvisited_onamountavg2019-01-07860122.862019-01-08840120.002019-01-09840120.00📈 Track restaurant growth with 7-day moving averagesOnly dates with complete 7-day history are included in results
Understanding the Visualization
1
Input
Customer transactions by date
2
Window Function
7-day sliding window calculation
3
Output
Moving averages for growth analysis
Key Takeaway
🎯 Key Insight: Use window functions with frame clauses for efficient moving average calculations in SQL

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