### 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
```