Oracle SQL – create select statement which will retrieve every end date of the month but compare the values the day or two after the end of month

Let’s say I have a customer table.

customer_name ||   date  ||  amount
-----------------------------------
A               31-OCT-20    100
A               01-NOV-20    100
A               02-NOV-20    200
B               31-OCT-20    300
B               01-NOV-20    325
B               02-NOV-20    350

I need to create a select statement which will retrieve every end date of the month and compare the values for the amounts respective to the day or two after. If the amount for the day or two is different from the end date of that month, display the recent changed amount.

Example 1 – Retrieve customer A for 31-OCT-20, compare to 01-NOV-20 and 02-NOV-20, output 200 for the amount.

Example 2 – Retrieve customer B for 31-OCT-20, compare to 01-NOV-20 and 02-NOV-20, output 350 for the amount.

Answer

Hmmm . . .

select t.*,
       (case when next_amount <> amount or next2_amount <> amount
             then greatest(next_amount, next2_amount)
             else next_amount
        end) as imputed_next_2_days
from (select t.*,
             lead(amount) over (partition by customer_name order by date) as next_amount,
             lead(amount, 2) over (partition by customer_name order by date) as next2_amount
      from t
     ) t
where date = last_day(date);