How to populate new column by comparing one row with its next row

Question

I am finding difficulty in writing a query to get the desired output. I need to get date difference between two consecutive rows and If date difference is <=6 months and reason code is valid "Yes" Then new column should be populate with values "Yes" other wise "NO"

I have to populate new column with values "Yes" or "NO" based on criteria given below
Criteria 1:
For first row, New column value should be always "No" From second row, If two dates difference is <=6 moths and reason code is valid then new column value should be "Yes" other wise "No"
Date difference of first row and second row is <=6 months and code valid "yes" hence new column value is Yes.
Date diff of second row and third row is <=6 months but valid is "No" hence new column value is "No".
Date diff of third row and forth row is more than >6 months and but valid is "Yes" hence new column value is "No".
Out put should be like below

Custid	enter_date	Rscode	Valid	New_column
123	2020-04-11	BCB	Yes	No
123	2020-06-13	ABC	Yes	Yes
123	2020-09-01	FBS	No	No
123	2021-05-01	ABC	Yes	NO
123	2021-07-05	ABC	Yes	Yes

Criteria 2:
If reason code is starts with "RQT" and any of the valid code is followed By RQT(even date difference is 6 months and reason code is valid) then new column should be "No" from next consecutive row, the output is same as is.

Custid	enter_date	Rscode	Valid	New_column
345	2020-02-19	RQT	Yes	NO
345	2020-03-22	BCB	Yes	NO
345	2020-06-18	RQT	Yes	Yes
345	2020-10-29	BCB	Yes	Yes
345	2021-03-24	ABC	Yes	Yes

Custid	enter_date	Rscode	Valid	New_column
346	2020-02-19	RQT	Yes	NO
346	2020-03-22	RQT	Yes	NO
346	2020-06-18	RQT	Yes	Yes
346	2020-10-29	BCB	Yes	Yes
346	2021-03-24	RQT	Yes	Yes

Answer 1

You need some Window Functions plus CASE like this:

case 
       -- If reason code is starts with "RQT"
  when first_value(Rscode) over (partition by custid order by enter_date) = 'RQT'
       -- and any of the valid code is followed By RQT
   and lead(Rscode) over (partition by custid order by enter_date) = 'RQT' then 'No'

       -- If two dates difference is <=6 moths and reason code is valid then new column value should be "Yes" other wise "No"
  when enter_date <= add_months(lag(enter_date) over (partition by custid order by enter_date), 6)
   and valid = 'Yes'
    then 'Yes'

  else 'No' -- also covers: For first row, New column value should be always "No"
end