I want to calculate a 10 day rolling sum. The problem is that the time is not regular, eg sometimes we have a couple of observation on the same date and then some dates are missing in between.

I have a table of rows in bigquery that has a userid and timestamp field createddate. I want the query to output userid, window_start_date, count(*) grouped by userid, window_start_date. The window_start_date is supposed to be the first row in the 24 hour window that starts at the first row after the previous 24 hour window ended. I wish I could base all the window_start dates on the days from the first row for each user but, a row can appear at any time and there could be gaps. For example, for one user, the original data would look like:

I am looking at a series of session dates of a user, and I am trying to “collapse” the event counts in a way that any event occurring in a 7 day time window is only counted once, starting from the first event and then again after the initial period of 7 days is over, and a new event (and a 7 day window) starts again.