3-Day Moving Average of Sales - Hard SQL Window Functions Interview Problem

Problem

The analytics team wants to smooth out daily sales noise using a rolling average. Using the sales table, return sale_date, amount, and a moving_avg column containing the 3-day moving average (current day plus the two preceding days), rounded to 2 decimal places. Order by date.

Schema

sales

column	type
id	INTEGER
sale_date	DATE
amount	NUMERIC

Sample Data

id	sale_date	amount
1	2024-01-01	100
2	2024-01-02	200
3	2024-01-03	150
4	2024-01-04	300
5	2024-01-05	250

Expected Output

sale_date	amount	moving_avg
2024-01-01	100	100.00
2024-01-02	200	150.00
2024-01-03	150	150.00
2024-01-04	300	216.67
2024-01-05	250	233.33

Hints