This kind of query would be able to provide the data for the profile.
with numberedrows as(
row_number() over (partition by UserID order by CreationDate desc) as NumRowsFromToday,
datediff(day,CreationDate,getdate())+1 as DaysFromToday,
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
where DaysFromToday = NumRowsFromToday
group by UserID
order by max(DaysFromToday);
Here's the idea behind the query:
I know from http://stackoverflow.com/questions/1176011/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?