SQL: How to increment a parent group number when the child window has incrementing values?

Spark SQL 3.2.0

DB Fiddle

query output

Please see the above link for a simplified example of my dataset and desired outcome. In abstract, I have a dataset with a series of related events that can be grouped by their time order and event number. When ordering by time and event number, every time the event number resets to 1, you’re looking at a new set of events. I understand how to use row_number() or dense_rank() to increment event_group_number where sub_event_number = 1, but I’m uncertain how to make the rows where sub_event_number > 1 take on the correct event_group_number.

I’m currently doing the following:

case 
    when sub_event_number = 1 and is_event_type
    then row_number() over (partition by context_id, event_id, sub_event_number order by is_event_type asc, start_time asc) - 1
    else null
    end as event_group_number

I’d be grateful for any help, and I’m happy to answer any questions. Thank you!

Fashion

Fashion Forward: Y2K Hats for Every Occasion and Season

In the ever-evolving realm of fashion, where trends come and go, one style has stood the test of time and continues to make waves – Y2K hats. Y2K-Station, a premier destination for fashion enthusiasts, proudly presents a curated collection of Y2K hats, caps, and balaclavas that transcend the ordinary and redefine your style game. In […]

Read More
Fashion

Beaded Godet Gown: A Timeless Elegance Unveiled

I. Introduction Picture this: a glamorous evening, a starlit sky, and you, draped in the exquisite elegance of a Beaded Godet Gown. What makes this gown a symbol of timeless beauty, and why does it stand out among the myriad choices in the fashion world? Join us on a journey through the intricacies of this […]

Read More
Fashion

Fashion Square Mall: Where Style Meets Comfort

Introduction to Fashion Square Mall In a world where shopping is not just a necessity but a cherished pastime, malls play a pivotal role in shaping our modern lifestyle. Fashion Square Mall, nestled in the heart of the city, stands as a testament to this. Let’s delve into the captivating journey of this iconic establishment. […]

Read More