Watch 10 video solutions for Restaurant Growth, a medium level problem involving Database. This walkthrough by Learn With Chirag has 12,634 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Customer
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | | visited_on | date | | amount | int | +---------------+---------+ In SQL,(customer_id, visited_on) is the primary key for this table. This table contains data about customer transactions in a restaurant. visited_on is the date on which the customer with ID (customer_id) has visited the restaurant. amount is the total paid by a customer.
You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
Return the result table ordered by visited_on in ascending order.
The result format is in the following example.
Example 1:
Input: Customer 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 | | 3 | Jade | 2019-01-10 | 150 | +-------------+--------------+--------------+-------------+ 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 | +--------------+--------------+----------------+ Explanation: 1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86 2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120 3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120 4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86
Problem Overview: The table records how much money customers spent at a restaurant on each day. The task is to compute a 7‑day rolling revenue: for every day starting from the 7th recorded day, calculate the total amount spent in the last 7 days and the average daily revenue for that window.
Approach 1: Sliding Window Aggregation (O(n) time, O(1) extra space)
Think of the data as a time‑ordered list of daily revenue values. First aggregate the table by visited_on so each row represents the total revenue for that day. Then maintain a 7‑day window that slides forward one day at a time. When the window moves, subtract the revenue that falls out of the window and add the new day’s revenue. This avoids recomputing the full 7‑day sum every time. The running sum directly gives the total amount, and dividing by 7 produces the average. This pattern is a classic sliding window technique used for fixed‑length range calculations.
Approach 2: SQL Window Function (O(n) time, O(1) extra space)
Modern SQL engines support window functions that compute rolling aggregates efficiently. After grouping the table by visited_on, apply SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). This automatically computes the 7‑day rolling total for each row. The average is simply that sum divided by 7, usually formatted with ROUND(..., 2). Finally filter out rows that don’t have a full 7‑day window. This approach is concise and optimized by the database query planner, making it the preferred solution for problems involving time‑based analytics in SQL or general database queries.
Recommended for interviews: Interviewers typically expect you to recognize the rolling‑window pattern. Explaining the manual sliding window demonstrates algorithmic thinking and shows you understand how repeated range sums can be optimized from O(n*k) to O(n). In database interviews or SQL rounds, the window function solution is the cleanest and most production‑ready approach because it expresses the rolling computation declaratively.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Sliding Window Aggregation | O(n) | O(1) | When implementing the logic in general programming languages like Python, Java, or C++ |
| SQL Window Function | O(n) | O(1) | Best for database queries where rolling aggregates can be expressed directly in SQL |
