SQL populating leading rows with conditions

Question

I have a table like below

if column condition is populated then for all rows after that row, populate column new column with value flag if other condition column contains x and episode start and episode end for that row matches with the earlier closest row where condition column contains yes

final output:

index   name    condition   episode_start   Episode_end other_condition  value     new
1         a                           11       12           
2         a                            1        2           
3         a          yes               1        2           
4         a                            1        2           
5         a                            1        2              x          f       flag
6         a                            3        4              b          cc    

---

#update1 below suggested answer works.

But not sure how to make this change

In the current case, we are looking for specific conditions in rows that come after the row where column condition=yes

instead of looking at previous rows, is there a way to look at rows that come after the current row?

Answer 1

If I follow this correctly, you can use window functions:

select t.*,
    case when other_condition = 'x'
        and max(condition) over(partition by episode_start, episode_end order by index) is not null
    then 'flag'
    end as newcol
from mytable t

Once a non-null condition is met, this flags all subsequent rows have the same episode_start and episode_end and a non-null value in other_condition.