Oracle SQL: LAG over a range of values

Question

How do we use analytic function LAG over the range of values. It should return null if there is no record in the partition with an earlier in_date otherwise, year and month of the previous in_date

Example:

CID  IN_DATE        
1    2020-05-29
1    2020-06-10
1    2020-06-21
1    2020-07-08
1    2020-08-11
1    2020-10-01
2    2020-05-05
2    2020-05-03
2    2020-06-01
2    2020-06-02
2    2020-06-03

Expected Output,

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
1    2020-10-01     2020-08
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
2    2020-07-03     2020-06
2    2020-08-13     2020-07

My current query using LAG returns below

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, 
           LAG(to_char(TO_DATE(IN_DATE), 'YYYY-MM')) OVER (PARTITION BY CID 
                                                           ORDER BY to_char(TO_DATE(IN_DATE), 'YYYY-MM') ) LAG 
    from data

Current Result

    CID IN_DATE     LAG
1   1   2020-05-29  NULL
2   1   2020-06-10  2020-05
3   1   2020-06-21  2020-06
4   1   2020-07-08  2020-06
5   1   2020-08-11  2020-07
6   2   2020-05-05  NULL
7   2   2020-05-03  2020-05
8   2   2020-06-01  2020-05
9   2   2020-06-02  2020-06
10  2   2020-06-03  2020-06

Looks like, LAG does not allow us to use partition over range Is there an alternate approach for this

Answer 1

You don't want LAG() if you want the previous month before this one. I would suggest:

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;

Here is a db<>fiddle.