postgresql query result using where condition another query

Question

my goal is detect all usernames and count each username login time in 24 hours. I was rite code separately but i cant combine it.

SELECT
   DISTINCT "username"
FROM
   my_table;

my second query is get me count log related this username

select count(*) 
from my_table as "foo" where "username" = 'example_username' and  "foo"."timestamp" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()

ı want to write one query this query detect unique/distinct usernames and return me last 24 hours logs for each username

if we think this query return a variable list

SELECT
   DISTINCT "username"           
FROM
   my_table;

variable_list =(username1,username2,...)

run that query for each username in variable_list

select count(*) 
from my_table as "foo" where "username" = 'example_username' and  "foo"."timestamp" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()

how can ı do that ? and what is the right way for solve this problem ?

Gordon Linoff · Answer 1 · 2020-08-04T14:29:37.793

0

I think you just want a WHERE clause. Assuming that you have no future timestamps:

SELECT DISTINCT username         
FROM my_table
WHERE timestamp >= NOW() - INTERVAL '24 HOURS' ;

If you want to count users in the last 24 hours, use aggregation:

SELECT username, COUNT(*) FILTER (WHERE timestamp >= NOW() - INTERVAL '24 HOURS')     
FROM my_table
GROUP BY username;

edited Aug 04 '20 at 14:29

answered Aug 04 '20 at 14:00

Gordon Linoff

1,242,037
58
646
786

This will not display all usernames, just the ones that logged on in the last 24h. So you will have no 0 counts. – Gabriel Durac Aug 04 '20 at 14:03

score 0 · Accepted Answer · answered Aug 04 '20 at 14:01

Use CTE to make a list of users and then just use a left join with aggregation.

WITH user_list as (SELECT
   DISTINCT "username"
   FROM
   my_table)
SELECT 
  user_list."username"
  ,count(*) 
FROM user_list 
LEFT JOIN my_table as "foo" 
   ON "foo"."username"=user_list."username"
WHERE  "foo"."timestamp" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
GROUP BY user_list."username"

postgresql query result using where condition another query

2 Answers2