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!

Beatrice Fisher

Types of Blouses
Fashion

Types of Blouses

Blouses can seem like an intimidating and complex piece of women’s clothing, but they don’t have to be! Blouses come in a wide variety of styles, from sweet and feminine to super sexy, so there’s sure to be one that fits your taste. If you want to look flirty, fun, and fashionable, here are the […]

Read More
Feel Great and Full of Confidence with Pantsandsocks.Com
Fashion

Feel Great and Full of Confidence with Pantsandsocks.Com

PantsandSocks.com, the online men’s branded underwear retailer, is urging men to “Pull-on the right pants, every time”, believing body confidence starts with your pants and socks, the unsung heroes of your wardrobe. Clothes play a big part in how we feel, how we speak to people, and how we position ourselves in different environments. The […]

Read More
Tips For Buying Bespoke Ties In 2022
Fashion

Tips For Buying Bespoke Ties In 2022

Whilst there is a wide range of options available to buy online and off the rack, purchasing bespoke ties can help you select a product that is perfect for you. The tie is a time-honoured tradition in Britain. An important element of corporate and formal wear, it can also be a casual or stylish inclusion […]

Read More