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:

    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!


The Art of Planning a Memorable Surprise Proposal

In the realm of love, few moments hold the same weight and significance as a surprise proposal. It’s that exhilarating instant when two souls intertwine their destinies, embarking on a journey of lifelong companionship. A surprise proposal is not just a gesture; it’s a testament to the depth of one’s love and commitment. Crafting such […]

Read More

The Unveiling of Loose Lab Grown Diamonds: Sparkling Elegance with Sustainable Brilliance

In a world where sustainability and ethical sourcing are becoming increasingly crucial considerations, the spotlight is turning towards Loose Lab Grown Diamonds. These gems offer a breathtaking brilliance akin to their mined counterparts but with a conscience-clearing difference. This article delves into the realm of Loose Lab Grown Diamonds, exploring their attributes, benefits, and why […]

Read More

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