CODEWITHSUNDEEP
sqlgoogle-bigquery
correct-membership
correct-membership

Reputation: 35

Incremental values when a condition is met in if statement

I've been stuck on this one for quite some time now and I can't figure it out. Here's my problem: I have two boolean columns condition_1 and condition_2, and I want to create a third column inc where the value increments each time this condition if condition_2 is false and lead(condition_1) over(partition by column_x order by column_y) is false is met.

The result would look something like that:

column_x     column_y     condition_1   condition_2     inc
A            12/03/2020   true          true            1
A            13/03/2020   true          false           1
A            14/03/2020   false         false           2
A            15/03/2020   false         true            3
A            16/03/2020   true          false           3
A            17/03/2020   false         true            4

Doing something like

Does someone have an idea?

Thanks a lot!

Upvotes: 0

Views: 925

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You describe this formula:

select t.*,
       countif( (not condition_2) and (not next_1)) over (partition by column_x order by column_y)
from (select t.*,
             lead(condition_1) over (partition by column_x order by column_y) as next_1
      from t
     ) t;

If you want the numbers to start at 1, then you need to add "1" to the value.

Upvotes: 1

Related Questions