Is there any way of doing while loop for dates to get 12 month rolling sales amount in Presto SQL?

Question

I want to implement below logic in Presto SQL:

DECLARE @StartDate DATE = '2016-10-01' ; DECLARE @EndDate DATE = '2021-05-02' ;

WHILE (@StartDate <= @EndDate) BEGIN
print @StartDate;

DECLARE @12MonthStartDate date
select @12MonthStartDate = (SELECT DateAdd(YEAR, -1, @StartDate));

DECLARE @12MonthEndDate date
select @12MonthEndDate = @StartDate ;

select * from MyTable where Date >= @12MonthStartDate and Date <= @12MonthStartDate

Based on above results, we need to do the rolling 12 months and insert the max date values into the table.

Note: we are using Presto SQL in Amperity tool.

set @StartDate = DATEADD(day, 1, @StartDate);

END;

Can any one please help with how to implement above logic in Presto SQL?

Thanks

Answer 1

You can compute a rolling sum with a window function:

SELECT dt, 
   sum(amount) OVER (
       ORDER BY dt 
       RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND CURRENT ROW)
FROM my_table

For every row in my_table, it looks at all the rows ordered by dt (ascending). Then it restricts the windows to rows whose dt is between 12 months before the dt of the current row and the dt of the row. Finally, it computes the sum of amount for all the rows in that window.

See this blog post for an explanation of how RANGE window frames work.