Teradata SQL- Order by date and flag rows between two events

I have a dataset like this

Where I have a field called, ‘SALES_CHANGE’ which identifies on which date sales either significantly rose or dropped. I’m interested in creating another field that flags periods between drops and rises. I.e., like this: Where the flag starts when there is a drop and then stops right before the next rise. And then starts again when there’s another drop.

Answer

Teradata supports lag() ignore nulls. So, your rule is basically that the current row or most recent row with a value is 'DROP'. For that . . .

select t.*,
       (case when sale_change = 'DROP' or prev_sale_change = 'DROP'
             then 'DIP'
        end)
from (select t.*,
             lag(sale_change ignore nulls) over (order by id_date) as prev_sale_change
      from t
     ) t;

You don’t really need the subquery — the lag can be in the outer case expression.