CODEWITHSUNDEEP
sqlpostgresql
Patanouk
Patanouk

Reputation: 141

Select count of consecutive events based on time column

Here is my data : 

app_id  event_type  event_time
1       event1      2020-22-03 04:05:03
1       event3      2020-22-03 04:05:04
1       event2      2020-22-03 04:05:05
1       event3      2020-22-03 04:05:10
1       event1      2020-22-03 04:05:11
1       event2      2020-22-03 04:05:12
2       event3      2020-22-03 04:05:04

I want to count the sum of two events for each app_id :

  1. Number of event3
  2. Number of event1 followed immediately (based on event_time) by event2

With the data above, the output would be : 

app_id  count
1       3 <--- (2 * event3 + event1 followed by event2)
2       1

The number of event3 can be calculated with : 

SELECT app_id,
    count(event_type = 'event3')
    FROM test_table
    GROUP BY app_id;

For the second count, I guess the data need to be GROUP BY app_id combined with ORDERBY event_time

I also saw other answers for checking consecutive values (Count Number of Consecutive Occurrence of values in Table), but I couldn't adapt it to my use case

Upvotes: 2

Views: 254

Answers (1)

forpas
forpas

Reputation: 164064

Use a CTE where you convert the 2 conditions to integers 0 or 1 which then will be aggregated:

WITH cte AS (
  SELECT *, 
    (event_type = 'event3')::int is_event3,
    (event_type = 'event1' AND LEAD(event_type) OVER (PARTITION BY app_id ORDER BY event_time) = 'event2')::int is_event1_event2
  FROM test_table   
)  
SELECT app_id, SUM(is_event3 + is_event1_event2) "count"
FROM cte
GROUP BY app_id

See the demo.
Results:

| app_id | count |
| ------ | ----- |
| 1      | 3     |
| 2      | 1     |

Upvotes: 2

Related Questions