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

Cat Hoodies: Feline Fashion Frenzy at Cat Shirts USA

In the realm of pet fashion, one trend has clawed its way to the top – cat hoodies! At CatShirtsUSA, the collection of cat print sweaters and hoodies is nothing short of a feline fashion feast. Let’s dive into the world where style meets comfort for our beloved four-legged companions. The Purr-ks of Cat Hoodies […]

Read More
Fashion

When Should You Wear Blue Light Glasses?

Blue light, which is emitted by electronic devices such as computers, smartphones, and televisions, is blocked by BLG eyewear. Eye strain, headaches, and sleep disturbances have all been linked to blue light exposure.. For more information on light glasses check out blue light glasses. In this, we will discuss when you should wear glasses to […]

Read More
Fashion

What Are The Features Of The Utility Kilt?

Utility kilts for men are a modern version of the traditional Scottish kilt, designed for practicality and functionality. Here are some common features of a utility kilt: Material: Utility kilts are typically made from durable, lightweight materials such as cotton, denim, canvas, or ripstop fabric. Pockets: One of the main features of a utility kilt […]

Read More