CODEWITHSUNDEEP
mysqlsql
Zabs
Zabs

Reputation: 14142

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

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;

Upvotes: 1

Views: 1545

Answers (3)

Zakaria
Zakaria

Reputation: 4806

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

Upvotes: 2

Akina
Akina

Reputation: 42622

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

Upvotes: 4

Zabs
Zabs

Reputation: 14142

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;

Upvotes: 0

Related Questions