How to track that a user visited the site each day for X days?

Question

There is a new badge on Stack Overflow. The "woot" badge is awarded to users visited the site each day for 30 days. How can you implement a feature like this? How can you track that a user visited the site each day for X days in the simplest way?

I thought of having two fields--one for the timestamp of the last login, and another to count the days on user continuously visited the site. The logic is to first set the counter to 1, and store the time of login as well. On the very next login, check if since the last login no more than one day past, and increment the counter, or set it back to 1. And then update the timestamp field to the current date.

Can you do it simpler?

Answer 1

You do need to have a cookie, since people might not log in every day -- for example because they are logged in automatically for 2 weeks, or because they are on your site doing things non-stop without sleep and lunch for 50 hours :) You probably actually want to count when user accesses the site.

Now, one could theoretically record every access and perform database queries, as was suggested above, but you might think (as I do) that it strikes the wrong balance between usefulness and privacy+simplicity.

The algorithm you specified is deficient in an obvious way: since you store only whole number of days, you miss the user who logs in and out every 12 hours (your algorithm would keep the count of days as 1)

Here's the solution that I find to be cleanest with two date fields per user, in a kind of self-explanatory non-object oriented Python:

# user.beginStreak----user.lastStreak is the last interval when 
# user accessed the site continuously without breaks for more than 25h 

def onRegister (user):
    ...
    user.beginStreak = user.endStreak = time() # current time in seconds 
    ...

def onAccess (user): 
    ...
    if user.endStreak + 25*60*60 < time():
        user.beginStreak = time()
    user.endStreak = time()
    user.wootBadge = ( user.endStreak-user.beginStreak > 30*24*60*60 )
    ...

(Please forgive my Pythonic skills, I'm an academic and first-time site user)

You cannot do this task with one variable. I'm sure somebody can write a clean argument proving this fact.

Answer 2

Actually, if the Member's visits are in a SQL database, you can do the whole thing with a single SQL query. This is also probably faster than schlepping all of the data over to a client program to check it anyway:

/*
    Find all members who visited at least once every day
  for 30 or more days.  --RBarryYoung, 2009-05-31
*/
;WITH
  cteVisitDays as (
    Select
      MemberID
    , DATEDIFF(dd,'2008-06-01',VisitTime) as VisitDay
     From tblMembersLog
     Where Not Exists( Select * From tblMemberTags T
    Where T.MemberID = tblMembersLog.MemberID
     And T.TagName = 'WOOT!' )
     Group By MemberID
        , DATEDIFF(dd,'2008-06-01',VisitTime)
    )
, cteVisitRunGroups as (
    Select 
      MemberID
    , VisitDay - Row_Number() Over(
            Partition By MemberID
            Order By VisitDay
        ) as RunGrouping
     From cteVisitDays
     )
SELECT Distinct
  MemberID
 From cteVistRunGroups
 Group By MemberId, RunGrouping
 Having COUNT(*) >= 30