Group similar consecutive events together in PostgreSQL

user_10022802 0
Viewed 74

I am new with SQL. I am currently learning using a dataset that has user events ordered by time. For example a sample subset is below -

EventID UserID EventName Timestamp
1 1 search_event 2020-01-20 09:42:52
2 1 search_event 2020-01-20 09:42:58
3 2 search_event 2020-01-20 09:43:27
4 1 checkout_event 2020-01-20 09:43:49
5 2 checkout_event 2020-01-20 09:43:54
6 2 search_event 2020-01-20 09:44:12
7 1 search_event 2020-01-20 09:54:21
8 1 search_event 2020-01-20 12:45:10
9 1 search_event 2020-01-20 12:45:32
10 2 booking_event 2020-01-20 12:46:52

I would like to count total distinct events in 10 min intervals since the first event occurred.

  1. User 1 made a search_event at 09:42:52. For the next 10 mins i.e. till 09:52:52 every search event is not counted for User 1. (i.e. the one at 09:42:58 is omitted in the count)
  2. User 2 made a search_event at 09:43:27. Hence till 9:53:27 none of the search_events will be counted for User 2.
  3. User 1 made a checkout_event at 09:43:49. Omit counting of all checkout events by User 1 till 09:53:49
  4. User 2 made a checkout_event at 09:43:54. Omit counting of all checkout events by User 2 till 09:53:54

Basically the following events are counted - (notified by event_ids)

Search - 1,3,7,8
Checkout - 4,5
Booking - 10

Output as count -

Search 4
Checkout 2
Booking 1

The following events are omitted due to time overlap.

Event ID 2 - Occurs within 10mins of Event ID 1
Event ID 6 - Occurs within 10mins of Event ID 3
Event ID 9 - Occurs within 10mins of Event ID 8

Thank you

postgresql sql
1 Answers

You can use simple not exists checking existence of the same records within 10 minutes before:

with a(EventID, UserID, EventName, ts) as (
  select 1, 1, 'search_event', timestamp '2020-01-20 09:42:52' union all
  select 2, 1, 'search_event', timestamp '2020-01-20 09:42:58' union all
  select 3, 2, 'search_event', timestamp '2020-01-20 09:43:27' union all
  select 4, 1, 'checkout_event', timestamp '2020-01-20 09:43:49' union all
  select 5, 2, 'checkout_event', timestamp '2020-01-20 09:43:54' union all
  select 6, 2, 'search_event', timestamp '2020-01-20 09:44:12' union all
  select 7, 1, 'search_event', timestamp '2020-01-20 09:54:21' union all
  select 8, 1, 'search_event', timestamp '2020-01-20 12:45:10' union all
  select 9, 1, 'search_event', timestamp '2020-01-20 12:45:32' union all
  select 10, 2, 'booking_event', timestamp '2020-01-20 12:46:52'
)
select *
from a
where not exists (
  select null
  from a as f
  where f.userid = a.userid
    and f.eventname = a.eventname
    and f.ts > a.ts - interval '10' minute
    and f.ts < a.ts 
)
order by eventid

eventid | userid | eventname      | ts                 
------: | -----: | :------------- | :------------------
      1 |      1 | search_event   | 2020-01-20 09:42:52
      3 |      2 | search_event   | 2020-01-20 09:43:27
      4 |      1 | checkout_event | 2020-01-20 09:43:49
      5 |      2 | checkout_event | 2020-01-20 09:43:54
      7 |      1 | search_event   | 2020-01-20 09:54:21
      8 |      1 | search_event   | 2020-01-20 12:45:10
     10 |      2 | booking_event  | 2020-01-20 12:46:52

db<>fiddle here

user_27787100
Related