This kind of query would be able to provide the data for the profile.
with numberedrows as(
select
row_number() over (partition by UserID order by CreationDate desc) as NumRowsFromToday,
datediff(day,CreationDate,getdate())+1 as DaysFromToday,
CreationDate,
UserID
from tablename
where UserID = @UserID /* Optional line that can be removed to get everyone's current run */
)
select UserID, min(CreationDate) as StartOfRun, max(DaysFromToday) as LengthOfRun
from numberedrows
where DaysFromToday = NumRowsFromToday
group by UserID
order by max(DaysFromToday);
Here's the idea behind the query:
I know from SQL to determine minimum sequential days of access? that they only store one entry per user per day (if not, then DENSE_RANK is needed instead of ROW_NUMBER, and the OVER clause needs to truncate the date). So if you number those days (backwards), then the current run of consecutive days only includes entries where the rownumber is the same as the number of days from then to today (plus one, so that you don't start counting that number of days from 0).
With an index on (UserID, CreationDate), this should run quickly too.
So... hopefully Jeff & co are keeping an eye on this question, and can consider it?
Rob
