Problem Description –
You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
Write an SQL query to 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 result table ordered by visited_on
in ascending order.
The query result format is in the following example.


Difficulty Level – Medium
Problem Link – Restaurant Growth
Solution –
WITH result as (
SELECT
visited_on,
SUM(amount) as amount
FROM customer
GROUP BY visited_on
), result2 as (
SELECT
visited_on,
SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as amount,
ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) as average_amount,
DENSE_RANK() OVER(ORDER BY visited_on) as rnk
FROM result
)
SELECT
visited_on,
amount,
average_amount
FROM result2
WHERE rnk > 6
To solve this problem first we have to group the data by visited_on and calculate the total amount.
SELECT
visited_on,
SUM(amount) as amount
FROM customer
GROUP BY visited_on

Next, we have to use 3 window functions and change their default value to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for the seven days window.
WITH result as (
SELECT
visited_on,
SUM(amount) as amount
FROM customer
GROUP BY visited_on
), result2 as (
SELECT
visited_on,
SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as amount,
ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) as average_amount,
DENSE_RANK() OVER(ORDER BY visited_on) as rnk
FROM result
)
SELECT * FROM result2

We almost has the result. All we need to do is exclude the top six results using the ranking column and select only the columns that is required in the solution.
WITH result as (
SELECT
visited_on,
SUM(amount) as amount
FROM customer
GROUP BY visited_on
), result2 as (
SELECT
visited_on,
SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as amount,
ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) as average_amount,
DENSE_RANK() OVER(ORDER BY visited_on) as rnk
FROM result
)
SELECT
visited_on,
amount,
average_amount
FROM result2
WHERE rnk > 6
