I'm trying to forecast the web traffic based on year over year change and based on the average YoY% on the same week day of the prior 4 weeks (windows function below:
AVG(YoY_Actual) OVER (partition by day_of_week, market order by calendar_date asc ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) as YoY_L4W%.
FYI I already have a calendar table which I joined with the actual traffic dataset so all the future dates show up in the table already.
Here's some sample data:
Since 1/4/22 is the last day there is actual data, some of the YoY_L4W% forecast come up as null.
Is there a way to use a loop in SQL to append the forecasted data to the table so that the YoY % will continuously show up to feed the calculation?