By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with data as (
select 1 CID, TO_DATE('2020-05-29','YYYY-MM-DD') IN_DATE from dual union all
select 1, TO_DATE('2020-06-10','YYYY-MM-DD') from dual union all
select 1, TO_DATE('2020-06-21','YYYY-MM-DD') from dual union all
select 1, TO_DATE('2020-07-08','YYYY-MM-DD') from dual union all
select 1, TO_DATE('2020-08-11','YYYY-MM-DD') from dual union all
select 2, TO_DATE('2020-05-05','YYYY-MM-DD') from dual union all
select 2, TO_DATE('2020-05-03','YYYY-MM-DD') from dual union all
select 2, TO_DATE('2020-06-01','YYYY-MM-DD') from dual union all
select 2, TO_DATE('2020-06-02','YYYY-MM-DD') from dual union all
select 2, TO_DATE('2020-06-03','YYYY-MM-DD') from dual
)
select CID,
to_char(TO_DATE(IN_DATE), 'YYYY-MM-DD') IN_DATE,
TO_CHAR(MAX(IN_DATE) OVER (PARTITION BY CID
ORDER BY TRUNC(IN_DATE, 'MON')
RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1' MONTH PRECEDING
),
'YYYY-MM') as LAG
from data
| CID | IN_DATE | LAG |
|---|---|---|
| 1 | 2020-05-29 | null |
| 1 | 2020-06-10 | 2020-05 |
| 1 | 2020-06-21 | 2020-05 |
| 1 | 2020-07-08 | 2020-06 |
| 1 | 2020-08-11 | 2020-07 |
| 2 | 2020-05-05 | null |
| 2 | 2020-05-03 | null |
| 2 | 2020-06-01 | 2020-05 |
| 2 | 2020-06-02 | 2020-05 |
| 2 | 2020-06-03 | 2020-05 |