CODEWITHSUNDEEP
database
Gerben Jacobs
Gerben Jacobs

Reputation: 4583

Keeping count of user metrics based around time

I want to keep count of some kind of achievements for users in a community based website. The idea is to give achievements for logging in 5 days in a row, or once every weekend for an entire month.

I'm also gonna give achievements for getting 100 posts, but this is easy to determine. The time based examples I just gave are a little harder I think.

How can I make some kind of generic system to keep count of these metrics per user? Or will I end up with a big table with fields such as "every_weekend_for_month" and "5_days_in_a_row" and once those integers reach 4 and 5, they have been 'achieved'. But then I also, for both fields, have to keep score of the last weekend/day.

Upvotes: 0

Views: 70

Answers (2)

Alex
Alex

Reputation: 7833

You will need to track all data that is (even partially) required to get the achievement.

For the achievements around logging in, you need to track each login once per day, having a table like:

user_id | login

1 | 2013-07-20
1 | 2013-07-19
1 | 2013-07-16
2 | 2013-07-20
...

Whenever the tracking event is triggered, you also check for the achievements.

event onLogin {

    // get the last 4 logins before the current login
    statement = (
        SELECT login FROM tracking_user_login
        WHERE user_id = 1
        ORDER BY login DESC
        LIMIT 1,4
    );
    statement.execute();

    // did the user even login at least 4 times already?
    if (statement.rowCount == 4) {

        date lastLogin = todaysLogin;
        int consecutiveLogins = 1;

        // iterate descending through the last days
        foreach (row in statement) {

            if (row.login == (lastLogin - 1day)) {
                consecutiveLogins++; // increment consecution
                lastLogin = (lastLogin - 1day); // prepare next comparison
            } else {
                // consecution interrupted, ignore the rest
                break;
            }
        }

        // enough to achieve something?
        if (consecutiveLogins >= 5) {
            user.addAchievement('5 CONSECUTIVE LOGINS');
        }
    }

}

You can basically add all achievements around login in this event.

Upvotes: 1

m4tt1mus
m4tt1mus

Reputation: 1641

You could track all logins and use that data to extrapolate the achievements. Tracking and searching individual logins can be slower. If you have very large user base and lots of logins, it may not be trivial to do these counts on login.

If you want to be faster you could track the last login date for a particular achievement and then increment a counter which sounds like what you were thinking.

Upvotes: 1

Related Questions