Get count of latest consecutive daily logins

Question

I have a SQL table containing a list of the daily logins of the subscribers to my site. The rows contain the user id and the date and time of the first login of each day, which means there is a maximum of one record per day for each member.

Is there a way to use SQL to get a count of the number if consecutive daily logins for each member, that is the latest login streak?

I could do this programmatically (C#) by going through each record for a user in reverse order and stop counting when a day is missing, but I was looking for a more elegant way to do this through a SQL function. Is this at all possible?

Thanks!

Try using Lag https://msdn.microsoft.com/en-IN/library/hh231256.aspx — Pream, Mar 07 '17 at 08:57
@Osprey, You should be able to solve this kind of problem using [Gaps and Islands solution](https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/) — Venu, Mar 07 '17 at 09:47
Thanks, @Venu but that appears to be to complicated. The LAG solution seems to be the easiest to implement (provided one has at least SQL 2012). — Osprey, Mar 07 '17 at 18:35

Pream · Accepted Answer · 2017-03-08T05:30:24.680

Answer from comment You can use Lag function https://msdn.microsoft.com/en-IN/library/hh231256.aspx

If your database compatibility level is lower than 110 you cant use Lag function

The following code must get the latest streak of logins for you (only when there is record for 1st login of the day)

suppose if your table of dates for a single user is

pk_id       dates
----------- -----------
27          2017-04-02 
28          2017-04-03 
29          2017-04-04 
30          2017-04-05 
31          2017-04-06 
44          2017-04-09 
45          2017-04-10 
46          2017-04-11 
47          2017-04-12 
48          2017-04-13

then

SELECT ROW_NUMBER() OVER(ORDER BY dates desc) AS Row#,dates into #temp1 FROM 
yourTable where userid = @userid

select top 1 a.Row# As LatestStreak from #temp1 a inner join #temp1 b on a.Row# = b.Row#-1 
where a.dates <> DATEADD(DAY,1,b.dates) order by a.Row# asc

this gives you 5, I have used Inner Join so that it wont have server compatibility issue

or you can use this, you can get the dates in the last streak too if you use c.dates instead of count(*)

SELECT COUNT(*)
FROM 
(SELECT MAX (a.dates) AS Latest
 FROM #yourtable a
WHERE DATEADD(DAY,-1,dates) 
    NOT IN (SELECT dates FROM #yourtable)) AS B
JOIN #yourtable c 
ON c.dates >= b.Latest

Somehow it is returning blank. Note that I am "cleaning" the date values by removing the time component. Anyway, I managed to get the value of the previous record using the LAG function as suggested by @Pream. I can then look for records where the DATEDIFF in days is greater than one and identify the latest gap. Then it is just a matter of counting the days since that occurrence. — Osprey, Mar 07 '17 at 18:17
Hi @Osprey,The answer belongs to the same Pream :D, I have just used self join instead of Lag in the answer, by cleaning to remove time component you mean, you are converting data type to date or varchar?? The query works fine if the data type of the dates column is date or datetime — Pream, Mar 08 '17 at 04:51
Hi @Pream. I was half asleep when I posted that. Lol. Anyway, by "cleaning" the date I mean converting "2017-03-08 08:47:34.670" to "2017-03-08 00:00:00.000". Your function works perfectly when the time stamps are "cleaned". Thanks! — Osprey, Mar 08 '17 at 11:11

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

This solution is probably similar to what you want:

How do I group on continuous ranges

It has a link to the motivating explanation here:

Things SQL needs: SERIES()

The main idea is that if after you have grouped by individual id's, and ordered by dates, the difference between date and current row is an invariant within each series of consecutive dates. So you group by user and this invariant (and min date within this group). And then you can group by user and pick the count of the 2nd column and only pick the max count.

edited May 23 '17 at 12:32

Community

1
1

answered Mar 07 '17 at 10:19

grovkin

158
1
3
9

Sounds good for finding the greatest login streak ever achieved, apart from the current one. Will test it as soon as the table in question is more populated. Thanks! – Osprey Mar 07 '17 at 18:40

Get count of latest consecutive daily logins

2 Answers2