A. Stam A. Stam - 4 months ago 11
SQL Question

SQL Rolling Summary Statistics For Set Timeframe

I have a table that contains information about log-in events. Every time a user logs in, a record is added containing the user and the date. I want to calculate a new column in that table that holds the number of times that user has logged in in the past 31 days (including the current attempt). This is a simplified version of what my table looks like, including the column I want to add:

UserID Date LoginsInPast31Days
-------- ------------- --------------------
1 01-01-2012 1
2 02-01-2012 1
2 10-01-2012 2
1 25-01-2012 2
2 03-02-2012 2
2 22-03-2012 1


I know how to calculate a total amount of login attempts: I'd use
COUNT(*) OVER (PARTITION BY UserId ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
. However, I want to limit the timeframe to the last 31 days. My guess is that I have to change the
UNBOUNDED PRECEDING
, but how do I alter it in such a way that it select the right amount of rows?

Answer

One pretty efficient way is to add a record 30 days after each date. It looks like this:

select userid, dte,
       sum(inc) over (partition by userid order by dte) as LoginsInPast31Days
from ((select distinct userid, logindate as dte, 1 as inc from logins) union all
      (select distinct userid, dateadd(day, 31, dte, -1 as inc from logins)
     ) l;
Comments