Styjun

This is a simplified version of my table

+----+----------+------------+------------+
| ID | Category | Start Date | End Date |
+----+----------+------------+------------+
| 1 | 'Alpha' | 2018/04/12 | 2018/04/15 |
| 2 | null | 2018/04/17 | 2018/04/21 |
| 3 | 'Gamma' | 2018/05/02 | 2018/05/07 |
| 4 | 'Gamma' | 2018/05/09 | 2018/05/11 |
| 5 | 'Gamma' | 2018/05/11 | 2018/05/17 |
| 6 | 'Alpha' | 2018/05/17 | 2018/05/23 |
| 7 | 'Alpha' | 2018/05/23 | 2018/05/24 |
| 8 | null | 2018/05/24 | 2018/06/02 |
| 9 | 'Beta' | 2018/06/12 | 2018/06/16 |
| 10 | 'Beta' | 2018/06/16 | 2018/06/20 |
+----+----------+------------+------------+

All Start Date are unique, not nullable and they have the same order as the IDs (if a and b are IDs and a < b then StartDate[a] < StartDate[b]). The Start Date is not always equal to the End Date of the previous row for the same Category (look at id 3 and 4).

I'm looking for a query that will give me the following result

+----------+------------+------------+
| Category | Start Date | End Date |
+----------+------------+------------+
| 'Alpha' | 2018/04/12 | 2018/04/15 |
| null | 2018/04/17 | 2018/04/21 |
| 'Gamma' | 2018/05/02 | 2018/05/17 |
| 'Alpha' | 2018/05/17 | 2018/05/24 |
| null | 2018/05/24 | 2018/06/02 |
| 'Beta' | 2018/06/12 | 2018/06/20 |
+----------+------------+------------+

Note: The End Date will be equal to End Date of the last row in the subgroup (same continuous Category).

This is a gaps-and-islands problem. I think you can use the difference of row numbers:

select category, min(startdate), max(enddate)
from (select t.*,
 row_number() over (order by id) as seqnum,
 row_number() over (partition by category order by id) as seqnum_c
 from t
 ) t
group by category, (seqnum - seqnum_c)
order by min(startdate);

This is a gaps and islands question, you can use such a logic below

select category, min(start_date) as start_date, max(end_date) as end_date
 from
(
select tt.*, sum(grp) over (order by id, start_date) sm 
 from
(
 with t( ID, Category, Start_Date, End_Date) as
 (
 select 1 , 'Alpha' , date'2018-04-12',date'2018-04-15' from dual union all
 select 2 , null , date'2018-04-17',date'2018-04-21' from dual union all
 select 3 , 'Gamma' , date'2018-05-02',date'2018-05-07' from dual union all
 select 4 , 'Gamma' , date'2018-05-09',date'2018-05-11' from dual union all
 select 5 , 'Gamma' , date'2018-05-11',date'2018-05-17' from dual union all
 select 6 , 'Alpha' , date'2018-05-17',date'2018-05-23' from dual union all
 select 7 , 'Alpha' , date'2018-05-23',date'2018-05-24' from dual union all
 select 8 , null , date'2018-05-24',date'2018-06-02' from dual union all
 select 9 , 'Beta' , date'2018-06-12',date'2018-06-16' from dual union all
 select 10 , 'Beta' , date'2018-06-16',date'2018-06-20' from dual
 ) 
 select id, Category, 
 decode(nvl(lag(end_date) over 
 (order by end_date),start_date),start_date,0,1) 
 as grp, --> means prev. value equals or not 
 row_number() over (order by id, end_date) as rn, start_date, end_date
 from t
) tt
order by rn
) 
group by Category, sm 
order by end_date;

CATEGORY START_DATE END_DATE 
Alpha 12.04.2018 15.04.2018 
NULL 17.04.2018 21.04.2018 
Gamma 02.05.2018 07.05.2018 
Gamma 09.05.2018 17.05.2018 
Alpha 17.05.2018 24.05.2018 
NULL 24.05.2018 02.06.2018 
Beta 12.06.2018 20.06.2018