The title is a bit confusing so the easiest think is to describe my problem using an example.

Assuming I'm having this dataset:

At first I want to achieve something like this:

So I want to add the same rnk value (probably rank is not the correct term) to all the records of the group until the next record has Is_new_session = True, in order to apply some aggregate functions similar and produce something like this:

Do you have any ideas on window functions that could help achieve this "ranking" (results shown on table no.2 )? T-SQL won't really work in my case. Any other recommendations for going from the 1st table to the 3rd without the need of the 2nd are of course very welcome.

CodePudding user response：

We use case when to mark every time event is true and then run a windw_function count() to divide the table into groups.

select   session_id 
        ,Username
        ,min(Event_time) as First_Event_time    
        ,max(Event_time) as Last_Event_time
        ,count(*)        as Event_counts
from     (
         select *
                ,count(case Is_new_session when 'True' then 1 end) over(order by Event_time) as session_id
         from   t
         ) t
group by Username, session_id

Fiddle