30 Days Of LeetCode Database Day 25 - 1321. Restaurant Growth

1Table: Customer
2 
3+---------------+---------+
4| Column Name   | Type    |
5+---------------+---------+
6| customer_id   | int     |
7| name          | varchar |
8| visited_on    | date    |
9| amount        | int     |
10+---------------+---------+
11In SQL,(customer_id, visited_on) is the primary key for this table.
12This table contains data about customer transactions in a restaurant.
13visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
14amount is the total paid by a customer.

1Example 1:
2 
3Input: 
4Customer table:
5+-------------+--------------+--------------+-------------+
6| customer_id | name         | visited_on   | amount      |
7+-------------+--------------+--------------+-------------+
8| 1           | Jhon         | 2019-01-01   | 100         |
9| 2           | Daniel       | 2019-01-02   | 110         |
10| 3           | Jade         | 2019-01-03   | 120         |
11| 4           | Khaled       | 2019-01-04   | 130         |
12| 5           | Winston      | 2019-01-05   | 110         | 
13| 6           | Elvis        | 2019-01-06   | 140         | 
14| 7           | Anna         | 2019-01-07   | 150         |
15| 8           | Maria        | 2019-01-08   | 80          |
16| 9           | Jaze         | 2019-01-09   | 110         | 
17| 1           | Jhon         | 2019-01-10   | 130         | 
18| 3           | Jade         | 2019-01-10   | 150         | 
19+-------------+--------------+--------------+-------------+
20Output: 
21+--------------+--------------+----------------+
22| visited_on   | amount       | average_amount |
23+--------------+--------------+----------------+
24| 2019-01-07   | 860          | 122.86         |
25| 2019-01-08   | 840          | 120            |
26| 2019-01-09   | 840          | 120            |
27| 2019-01-10   | 1000         | 142.86         |
28+--------------+--------------+----------------+
29Explanation: 
301st 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
312nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
323rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
334th 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
34 

1**SQL**
2 
3WITH daily AS (
4    SELECT visited_on , SUM(amount) AS daily_amount
5    FROM Customer GROUP BY visited_on
6)
7SELECT 
8    d1.visited_on,
9    SUM(d2.daily_amount) AS amount,
10    ROUND(SUM(d2.daily_amount) / 7 , 2) AS average_amount
11FROM daily d1
12JOIN daily d2
13    ON d2.visited_on BETWEEN DATE_SUB(d1.visited_on, INTERVAL 6 DAY) 
14    AND d1.visited_on
15GROUP BY d1.visited_on HAVING COUNT(d1.visited_on) = 7
16ORDER BY visited_on;