Leetcode: Restaurant Growth (SQL)

This question is asked at Amazon and Point72 and goes as follows:

Let's pretend you're a fancy restaurant owner. You want to analyze a possible expansion where there will be at least one customer every day. Let's write a SQL query to compute the moving average of how much a customer paid in a 7-day window, and we should round this amount to 2 decimal places. Here's what things look like so far:

Column Names: customer_id (int), name (varchar), visited_on(date), amount(int). This contains data about the customer transactions on the restaurant, and I want to see the average total paid by the customer. 

Example transaction
(1, John, 2019-01-01, 100), and I believe that there's going to be a date library in SQL. We want to return the table and average amount with the last date of visitation.  

We want to make sure that the days visited starts at 7 days. We also want to make sure that the rolling sum and rolling average are calculated on a 7-day window. We want to grup by the day that we are visited from, and we want to select the sum of A and B, and then finally round it to the hundredths place. 

So we want to select the visited_on and the sum from the sum of the amounts  where the date difference is between 0 and 7. Maybe we are trying to join a and b, I don't really know. Again we want the difference of the visited on dates to be 7 since we are summing up 7 days. Example January 1-7, January 2-8, January 3-9, etc. 

Here's the code anyways:

SELECT a.visited_on AS visited_on, SUM(b.day_sum) AS amount, 

    ROUND(AVG(b.day_sum), 2) AS average_amount (approximate the average solution to 2 decimal points) 

FROM

    (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on) a,

    (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on) b

WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6

GROUP BY a.visited_on (groups by the date)

HAVING COUNT(b.visited_on) = 7 (we want the difference of the days be 7 days)