How do I count events in chronological order in SQL?

Question

I'd like to aggregate Talbe_A and make it like Table_B. It is difficult for me to count events according to the time order. How should I write a Query?

Table_A

client_id   event   time
   A        view        12:00:00
   A        view        12:02:00
   A        view        12:05:00
   A        purchase    14:02:00
   B        view        12:04:00
   B        view        12:07:00
   B        view        13:20:00
   C        view        12:00:00
   C        view        12:07:00
   C        add_to_cart 14:02:00
   C        view        14:19:00
   C        purchase    14:32:00
   C        view        15:32:00

Table_B

client_id   event   count
    A       view        3
    A       purchase    1
    B       view        3
    C       view        3
    C       add_to_cart 1
    C       view        1 
    C       purchase    1
    C       view        1

Answer 1

This is a gaps-and-islands problem, where you want to group together "adjacent" rows.

Here I think that the simplest approach is to use the difference between row numbers to define the groups:

select client_id, event, count(*) cnt, min(time) start_time, max(time) end_time
from (
    select 
        t.*,
        row_number() over(partition by client_id order by time) rn1,
        row_number() over(partition by client_id, event order by time) rn2
    from mytable t
) t
group by client_id, event, rn1 - rn2
order by client_id, min(time)

Demo on DB Fiddle:

client_id | event       | cnt | start_time | end_time
:-------- | :---------- | --: | :--------- | :-------
A         | view        |   3 | 12:00:00   | 12:05:00
A         | purchase    |   1 | 14:02:00   | 14:02:00
B         | view        |   3 | 12:04:00   | 13:20:00
C         | view        |   2 | 12:00:00   | 12:07:00
C         | add_to_cart |   1 | 14:02:00   | 14:02:00
C         | view        |   1 | 14:19:00   | 14:19:00
C         | purchase    |   1 | 14:32:00   | 14:32:00
C         | view        |   1 | 15:32:00   | 15:32:00