There is an event table with the following structure: user_id id created_at (timestamp)

We need a request that pulls out all the entries added more than 30 minutes ago, gets user_id and pulls out all entries with this user_id in the last 30 minutes.

The task initially is this: you need to create a notification system. Accordingly, you need to send notifications in batches (several pieces), from one user "pack" should come no more than once every 30 minutes.

The user makes some edits in his profile, and then all these edits are grouped and sent to the admin (once every 30 minutes), in order not to clutter the edits for each user are grouped and sent in batches.

for example

| id | user_id | ------ | created_at

-1 ---- 5 ---------- a minute ago

-2 ---- 5 ---------- 5 minutes ago

For such a table, we do not pull anything out and for:

| id | user_id | ------ | created_at

-1 ---- 5 ---------- 31 minutes ago

-2 ---- 5 ---------- 5 minutes ago

We pull out all 2 records

  • We tried to implement it ourselves - Naumov
  • In 1 query does not work - Nikolai An
  • If it is not difficult to reformulate please the phrase "We need a request that pulls out all the entries added more than 30 minutes ago, receives user_id and pulls out all entries with this user_id in the last 30 minutes." - Need notifications in the last 30 minutes? It is not yet clear why users_id users should be previously retrieved, why not just retrieve messages in the last 30 minutes. - cheops pm
  • The fact of the matter is that there is not. Notifications for the last 30 minutes are needed if there is a notification added more than 30 minutes ago. - Nikolai An

1 answer 1

Depending on what percentage of users fall under the condition can be done in different ways.

If there are a lot of users who have had events in the last 30 minutes (more than 25% of all users of the system, we think), then this query will do:

 select * from event where user_id in( select user_id from event where created_at < now() - interval 30 minute ) and created_at >= now() - interval 30 minute

If there are few users, then by speed it may be more advantageous to request this:

 select * from event E where created_at >= now() - interval 30 minute and exists(select 1 from event E1 where E1.created_at < now() - interval 30 minute and E1.user_id=E.user_id )