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 ?

Answer 1

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"