Query to get all users that have logged in within the last 30 days-CodePudding

I have a MySQL db users table that has a column called lastLogin this is just a simple timestamp to indicate when a user has last logged into the system.

e.g

id -- name -- lastLogin -- accountId
2.     bob.    1639572638      4
3.     tim.    1639572638      4
3.     ant.    1639572638      5
4.     leroy.  1339572638      6

expected results

accountId  -- activeUsers
    4               2
    5               1
    6               0

My current query returns 0 rows but not sure why

SELECT accountId, from_unixtime(lastLogin) as lastlogin, count(distinct(id)) as activeUsers 
FROM user 
HAVING lastlogin > now() - INTERVAL 30 day 
ORDER BY lastlogin desc;

CodePudding user response：

SELECT accountId, 
       FROM_UNIXTIME(MAX(lastlogin)) lastlogin,  -- not listed in desired output
                                                 -- but present in the query
       SUM(lastlogin > UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY)) activeUsers 
FROM user
GROUP BY accountId

For distinct id use

SELECT accountId, 
       FROM_UNIXTIME(MAX(lastlogin)) lastlogin,
       COUNT(DISTINCT CASE WHEN lastlogin > UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY) THEN id END) activeUsers  
FROM user
GROUP BY accountId

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f754e9ed49d872d0d68173a803f96126

CodePudding user response：

Try this:

with u as
(select accountId, count(distinct id) as activeUsers from user
group by accountId
having FROM_UNIXTIME(max(lastlogin)) > now() - INTERVAL 30 day),
v as
(select distinct accountId from user)
(select v.accountId, coalesce(u.activeUsers, 0) as activeUsers from v left join 
u on v.accountId = u.accountId)

Fiddle

CodePudding user response：

okay i figured it out hopefully helps someone else -

SELECT accountId,count(distinct(id)) as activeUsers 
FROM user 
WHERE FROM_UNIXTIME(lastlogin) > now() - INTERVAL 30 day 
GROUP BY accountId;