Can anyone help with calculating sales figures for month-until-date rolling sum on a data table carrying Datetime column and Sales figures column?
Using OVER in standard sql can help me calculate rows/dates preceding the current row, but I am having trouble with starting from day one of a month.
If you create columns for day, month, year (see:date extract function), you can use the month and year in the "PARTITION BY" part of your OVER function and the day in the "ORDER BY" part.
UPDATE
AliveToLearn worked it out: AVG(events_US) OVER (Partition by event_month, event_year ORDER BY day) AS moving_avg_month