How to count the number of messages sent to a user?

Question

PostgreSQL

You need to select users who received more than N notifications in the last 24 hours.
There are two tables:
1. public.user

create table public.user
(
  "id" serial primary key,
  "email" varchar(30) unique not null
);

2.public.notification

create table public.notification
(
  "id" serial PRIMARY KEY,
  "user_id" serial REFERENCES public.user (id) ON DELETE CASCADE,
  "sent_timestamp" timestamp default current_timestamp
);

The script with which I select users. At the same time, the input is a list of users, so to speak "suspected", i.e. we are not looking for all-all users in the database, but only for a narrowed list.

SELECT public.user.email
    FROM public.user 
    WHERE public.user.email IN ('[email protected]', '[email protected]')
    AND 
    (
      SELECT COUNT (*) FROM
        (SELECT *
            FROM public.user 
            INNER JOIN public.notification ON public.user.id = public.notification.user_id
            WHERE public.notification.sent_timestamp > NOW() - (1440 * interval '1 minute')
        ) as c
        
      WHERE public.user.email IN ('[email protected]', '[email protected]')
      ) > 10
    ;

But as a result, it turns out that the total number of sent notifications is considered to be all "suspects".
Please help, I can't figure out how to do it for the second hour

Answer 1

SELECT *, count(*) as notifyCount FROM public.notification
here joins if needed
group by user_id
WHERE sent_timestamp > boo boo