Rolling 3 day average transaction amount for each day

Question

I'm trying to get the rolling 3 day average transaction amount for each day. I first grouped my data by day from the time stamp using cast:

select
    cast(transaction_time as Date) As Date
    , SUM(transaction_amount) as total_transaction_amount  
from transactions
Group by cast(transaction_time as date) 
order by cast(transaction_time as date)

now I want to get the rolling 3 day average:

select *,
  avg(transaction_amount) OVER(ORDER BY transaction_time
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
     as moving_average
from transactions;

but don't know how to make both statements work together, any ideas?

Answer 1

You've basically done all the hard work, just need to stick them together and a CTE is great for this.

    With transactions_by_day as(

select
    cast(transaction_time as Date) As Date
    , SUM(transaction_amount) as total_transaction_amount  
from transactions
Group by cast(transaction_time as date) 
order by cast(transaction_time as date))

select *,
  avg(total_transaction_amount) OVER(ORDER BY date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
     as moving_average
from transactions_by_day