Report number of users that have used each login method

[rishabh]

Number of active users per login method

Counts how many users have used each login method in the last 28 days. The time period can be changed via a parameter

--[params]
-- integer :last_seen_days = 28

SELECT provider_name, count(*)
FROM user_associated_accounts uaa
JOIN users u on u.id = uaa.user_id
WHERE u.active AND NOT u.staged
AND u.last_seen_at IS NOT NULL
AND last_used > NOW() - INTERVAL ':last_seen_days days'
GROUP BY provider_name
ORDER BY count(*) DESC

Output

[david (David Taylor)]

I just updated this query following a change to how we store GitHub login information.

It will now work for all core login methods, and any plugins which use the ‘ManagedAuthenticator’ system (including discourse-oauth2 and discourse-openid-connect)

I also added a last_seen_days parameter, so you can easily customize the query time period.