Restaurant Growth
Task: Compute the moving average of how much the customer paid in a seven days window.
Table: Customer
+-------------+-------------+
| Column Name | Type |
+-------------+-------------+
| customer_id | INT |
| name | varchar(20) |
| visited_on | date |
| amount | INT |
+-------------+-------------+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 |
+------------+--------+----------------+View Reference Solution & Explanation ▼
This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, a `HAVING` clause to filter the aggregated groups, the `ROUND()` function to format numerical output, a subquery as a derived table or inline value, a Common Table Expression (CTE) to simplify complex subqueries, and date functions to handle time-based filtering or grouping.
WITH Daily AS (SELECT visited_on, SUM(amount) AS daily_amount FROM Customer GROUP BY visited_on) SELECT d1.visited_on, SUM(d2.daily_amount) AS amount, ROUND(SUM(d2.daily_amount) / 7.0, 2) AS average_amount FROM Daily d1 JOIN Daily d2 ON d2.visited_on BETWEEN DATE(d1.visited_on, '-6 days') AND d1.visited_on GROUP BY d1.visited_on HAVING COUNT(d2.visited_on) = 7 ORDER BY d1.visited_on;