Here is one way to solve this, which will work at least as far back as Oracle 10.2. It uses analytic functions and a hierarchical query.
The WITH clause is there just to build the sample data on the fly. You don't need it - remove it, and use your actual table and column names in the query. (In the WITH clause I declared the columns after the CTE name, which works only in Oracle 11.2 and higher, but the WITH clause is not part of the solution, so I wouldn't worry about that.)
with
sample_data (country, date_id) as (
select 'USA', 199003 from dual union all
select 'USA', 200004 from dual union all
select 'USA', 200005 from dual union all
select 'USA', 200009 from dual union all
select 'USA', 200010 from dual union all
select 'UK' , 199307 from dual union all
select 'UK' , 199308 from dual union all
select 'UK' , 199408 from dual
)
select country, date_id
from (
select country, date_id,
row_number() over (partition by country order by dt) as rn,
count(*) over (partition by country order by dt
range between current row
and interval '4' month following) as ct
from (
select country, date_id,
to_date(to_char(date_id, 'fm999999'), 'yyyymm') as dt
from sample_data
)
)
start with rn = 1
connect by country = prior country and rn = prior rn + prior ct
;
COUNTRY DATE_ID
------- ----------
UK 199307
UK 199408
USA 199003
USA 200004
USA 200009
For comparison, here is a match_recognize solution, which requires Oracle 12.1 or higher:
select country, date_id
from (
select country, date_id,
to_date(to_char(date_id, 'fm999999'), 'yyyymm') dt
from sample_data
)
match_recognize(
partition by country
order by date_id
all rows per match
pattern (a {- b* -})
define b as dt < add_months(a.dt, 5)
);
