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.
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:
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?