Postgresql : How to display an additional column after an aggregate function

user_14143324 0
Viewed 65

I have two tables : users and sessions.

users has columns : username, id

sessions has columns : userid, lastactivityat, deleteat

I wish to extract all the users with the most recent "last activity date" session for each user and then filter the users with a "last activity date" older than x days. The "last activity date" is in epoch format and ms that's why I had to do some conversion calculation.

Here's my current request (with x = 30)

select u.username, min(extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int) as most_recent_inactivity_days 
from users as u 
  join sessions as s on s.userid=u.id 
where extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int >= 30 
group by username 
order by username

Now I wish to add to the result of my request : the deleteat column of each user but I failed with this request :

select u.username, min(extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int) as most_recent_inactivity_days, s.deleteat  
from users as u 
  join sessions as s on s.userid=u.id 
where extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int >= 30 
group by username 
order by username

Would you please advise?

aggregate-functions min join postgresql
1 Answers

This is a case for DISTINCT ON:

SELECT DISTINCT ON (u.username)
       u.username,
       extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int AS most_recent_inactivity_days,
       s.deleteat  
FROM users AS u 
   JOIN sessions AS s ON s.userid=u.id 
WHERE extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int >= 30
ORDER BY u.username,
         extract(epoch from now() - to_timestamp(lastactivityat/1000))/86400::int;

This nice query also shows why it is better to save timestamps as timestamps rather than integers; the query would be so much simpler that way.

user_64643080
Related