Getting rank today, this week and this month

In my previous article I’ve shown how to get rank using mysql query. Now I’m showing how to retrieve result based on today, week and month using mysql query. Actually I also implemented this in my quiz project so I’m sharing this with you.

For the table structure please look my previous article http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

Task 1: I’ve to retrieve those users rank who played the game today.
Solution: Look at the query

SELECT uid, participated, correct, wrong from quiz_user
    WHERE DAYOFMONTH(CURDATE())=extract(day from updated)
    ORDER BY correct DESC, participated ASC
    limit 30

So the above query returns the result of those users who played today. Here

CURDATE()

returns current date like ’2010-02-26′.

DAYOFMONTH(CURDATE())

This function returns day of the month based on the date. You have to provide the date as parameter. So if you provide DAYOFMONTH(’2010-02-26′) it will return 26.

I updated the update field of the table when user last played. So to get the last active day of the user I used

extract(day from updated)

This function retrieve only the day part from updated field. And at last I checked both days to get the activity of today

WHERE DAYOFMONTH(CURDATE())=extract(day from updated)

Task 2: I’ve to retrieve those users rank who played the game this week.
Solution: look at the query

SELECT uid, participated, correct, wrong from quiz_user
   WHERE updated >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
   ORDER BY correct DESC, participated ASC
   limit 30

Here I used

DATE_SUB(CURDATE(), INTERVAL 7 DAY)

DATE_SUB() function subtracts a time value (interval) from a date. So I first calculated what is the date of 7 days ago and then I just checked the user updated field is equal or greater than that date and thus I get the ranks of those users who played the game this week.

Task 3: I’ve to retrieve those users rank who played the game this month.
Solution: look at the query

SELECT uid, participated, correct, wrong from quiz_user
   WHERE updated >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
   ORDER BY correct DESC, participated ASC
   limit 30;

I think you’ll understand the above query as this query is almost same as week. So I hope this article may help you to understand some of the date and time functions in mysql.

Reference: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html