Rolling 7-Day Average Daily Sales

SQL coding challenge · Difficulty: easy · +50 XP

A retail company wants to analyze their sales trends by calculating a 7-day rolling average of daily sales. Given a 'sales' table, compute the rolling 7-day average of sales amount for each day, considering the current day and the previous 6 days. Return the date and the corresponding rolling average, ordered by date ascending.

Example input

`

sales table:

sale_id | sale_date | sales_amount

1 | 2024-01-01 | 150.00

2 | 2024-01-02 | 180.00

3 | 2024-01-03 | 200.00

4 | 2024-01-04 | 220.00

5 | 2024-01-05 | 250.00

6 | 2024-01-06 | 300.00

7 | 2024-01-07 | 350.00

8 | 2024-01-08 | 400.00

`

Expected output

`

sale_date | rolling_avg_7_days

2024-01-07 | 235.71

2024-01-08 | 271.43

`