Assign min value or max value based on a value change

HAVE

ID  cd dt       ct_vl
1   A  20210101 0
1   B  20210201 0 
1   A  20210301 0
2   A  20210101 0
2   B  20210201 1
3   C  20210101 0
3   V  20210201 1
3   C  20210301 0
3   C  20210401 0

WANT (Get min date when ct_val within the ID group is 0 across that ID group OR get max date within the ID group where ct_val is not 0 (that is it may contain a 1) across that ID group). For instance, if the ct_val within an ID group is 1, pick the maximum dt where ct_val within that group is 1

ID  cd dt       ct_vl final_dt
1   A  20210101 0      20210101
1   B  20210201 0      20210101
1   A  20210301 0      20210101 
2   A  20210101 0      20210201
2   B  20210201 1      20210201
3   C  20210101 0      20210301
3   V  20210201 1      20210301 
3   C  20210301 1      20210301
3   C  20210401 0      20210301

Answer

You can try to use MAX condition aggragte window function with FIRST_VALUE window function to get your logic.

Query 1:

SELECT t.*,
       coalesce(MAX(CASE WHEN ct_vl = 1 THEN dt END) OVER(PARTITION BY ID),FIRST_VALUE(dt) OVER(PARTITION BY ID ORDER BY dt)) final_dt
FROM T t

Results:

| ID | CD |       DT | CT_VL | FINAL_DT |
|----|----|----------|-------|----------|
|  1 |  A | 20210101 |     0 | 20210101 |
|  1 |  B | 20210201 |     0 | 20210101 |
|  1 |  A | 20210301 |     0 | 20210101 |
|  2 |  A | 20210101 |     0 | 20210201 |
|  2 |  B | 20210201 |     1 | 20210201 |
|  3 |  C | 20210101 |     0 | 20210301 |
|  3 |  V | 20210201 |     1 | 20210301 |
|  3 |  C | 20210301 |     1 | 20210301 |
|  3 |  C | 20210401 |     0 | 20210301 |