Counting events only once if an event happens more than once every X minutes

Question

I have a table that is filled everytime a user starts a session in my app. But I dont want to count their session more than once if they make it within 10 minutes. How can I do it?

Here's an example of what is returned from the table

   select
    *
    from table
    limit 100


+----------+--------+---------+----------------+
| event_ID | userid | city_id |   created_at   |
+----------+--------+---------+----------------+
|        1 | a      |       1 | 15/08/19 10:10 |
|        2 | b      |       1 | 15/08/19 10:11 |
|        3 | a      |       1 | 15/08/19 10:14 |
|        4 | a      |       1 | 15/08/19 10:25 |
|        5 | b      |       1 | 15/08/19 10:27 |
|        6 | c      |       1 | 15/08/19 10:30 |
|        7 | c      |       1 | 15/08/19 10:35 |
|        8 | d      |       1 | 15/08/19 10:40 |
|        9 | d      |       1 | 15/08/19 10:49 |
|       10 | c      |       1 | 15/08/19 10:55 |
+----------+--------+---------+----------------+

In the end, I would want to count the unique event_ids for each user, based on the premise that a unique event_id is defined by the amount of times it happens every 10 minutes

So it should be something like this in the end:

+--------+------------------+
| userid | unique_event_ids |
+--------+------------------+
| a      |                2 |
| b      |                2 |
| c      |                2 |
| d      |                1 |
+--------+------------------+
+--------+------------------+
| Total  |                7 |
+--------+------------------+

Any suggestion on how to start?

In your example d should result in 2 as well (40 and 51) – S-Man Aug 15 '19 at 15:12 — S-Man, Aug 15 '19 at 15:12
You're right, I forgot to edit. It's correct now – João Paulo Machado Aug 15 '19 at 17:41 — João Paulo Machado, Aug 15 '19 at 17:41

score 1 · Accepted Answer · answered Aug 15 '19 at 15:06

1

Use lag() to determine when the previous event was created for the user. Then some date filtering and aggregation:

select userid, count(*)
from (select t.*,
             lag(created_at) over (partition by userid order by created_at) as prev_created_at
      from t
     ) t
where prev_created_at is null or prev_created_at < created_at - interval '10 minute'
group by userid

answered Aug 15 '19 at 15:06

Gordon Linoff

1,242,037
58
646
786

But what if user 'a' has 4 events? |10:30; 10:35; 10:41; 10:42| I should have 2 unique events, but I guess it would count as only one, right? – João Paulo Machado Aug 15 '19 at 16:59
@JoãoPauloMachado . . . This counts it only once. I notice that you accepted an equivalent answer posted *7 minutes* after this one. I suppose you have your reasons. – Gordon Linoff Aug 15 '19 at 21:50
I mean I can only choose only one and I didn't think people would care about it that much. but you're right, you answered first, have you accepted answer – João Paulo Machado Aug 16 '19 at 12:46

score 1 · Answer 2 · answered Aug 15 '19 at 15:13

1

I would do:

select
  userid,
  sum(case when created_at - interval '10 minute' < prev then 0 else 1 end)
    as unique_events_ids
from (
  select
    *,
    lag(created_at) over(partition by userid order by created_at) as prev
  from t
) x
group by userid

answered Aug 15 '19 at 15:13

The Impaler

45,731
9
39
76

But what if user 'a' has 4 events? |10:30; 10:35; 10:41; 10:42| I should have 2 unique events, but I guess it would count as only one, right? Since the difference between them is never higher than 10 minutes – João Paulo Machado Aug 15 '19 at 17:04
Yes, using this logic it would count only once. This logic considers a "new sessions" starts when 10 minutes have passed since the last interaction. – The Impaler Aug 15 '19 at 17:06

Counting events only once if an event happens more than once every X minutes

2 Answers2