I have a table with date and amount columns. I got the 28 days rolling avg like this:
AVG(sum_of_amount) OVER (ORDER BY date RANGE BETWEEN INTERVAL 27 DAYS PRECEDING AND CURRENT ROW) AS 28_days_roll_avg
I would need the same roll_avg from the same period of previous year.
I got the sum_value from the beginning of this year till today like this:
SUM(sum_of_amount) OVER (PARTITION BY year(date) ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS amount_cum_cnt
I would need the sum of amount from the beginning of this year till the end of prev month. I would need the sum of amount from the beginning of previous year till the same day as today of prev year.
Can you help me out?
I just do not know how to specify the time range for previous year.