Obtaining Unique total users per month using SQL query

Obtaining Unique total users per month using SQL query

by JC Dodo -
Number of replies: 2

Hello everyone!

I have Moodle 2.5 and 2.6 versions. 

I am just wondering if the below SQL query is a correct approach to obtain unique total users every month?

SELECT id,username,from_unixtime(`lastlogin`) AS `days` from <database name>.<prefix>_user where date(from_unixtime(`lastlogin`)) between date ('2017-12-01 00:00:00') and date('2017-12-31 23:59:59');


I know that this can be done using this ad-hoc contributed report - Distinct user logins per month but this is only effective above version 2.7 of Moodle.


Any advise is deeply appreciated.

Thanks!

Average of ratings: -
In reply to JC Dodo

Re: Obtaining Unique total users per month using SQL query

by Darko Miletić -

I think this one would be more appropriate:

SELECT COUNT(p.userid) nrusers FROM (
SELECT l.userid, MONTH(FROM_UNIXTIME(l.time))
FROM mdl_log l
WHERE l.action = 'login' AND l.module = 'user' AND YEAR(FROM_UNIXTIME(l.time)) = 2018
GROUP BY l.userid, MONTH(FROM_UNIXTIME(l.time))
) p

The reason we do not do count in first query is because in recent iterations MySQL insists that all fields in SELECT clause must be present in the GROUP BY clause as well. You can read more about that here:

https://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/


Average of ratings: Useful (2)
In reply to Darko Miletić

Re: Obtaining Unique total users per month using SQL query

by JC Dodo -

Thanks Darko!

As I am new in MySQL, with that SQL query how would you specify to output the month and year i.e. december 2017 ?


Another question is would that count a for unique users?


Average of ratings: -