Hoang Chu's Ramp Applied Scientist Intern application

SELECT
    transaction_date,
    rolling_3_day_avg
FROM (
    SELECT
        transaction_date,
        total_amount,
        AVG(total_amount) OVER (
            ORDER BY transaction_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS rolling_3_day_avg
    FROM (
        SELECT
            DATE(transaction_time) AS transaction_date,
            SUM(transaction_amount) AS total_amount
        FROM transactions
        GROUP BY DATE(transaction_time)
    ) dt
) sub
WHERE transaction_date = '2021-01-31';