By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual
)
select
tmp.*,
dense_rank() over( order by startdate, username)-
(case when flag=1 then
row_number()over(partition by flag, username order by startdate, username) - flag
else null end) as grp
from tmp
order by
startdate, username
| USERNAME | STARTDATE | FLAG | GRP |
|---|---|---|---|
| username1 | 01-APR-19 | 1 | 1 |
| username1 | 01-APR-19 | 0 | null |
| username1 | 01-APR-19 | 1 | 2 |
| username1 | 01-APR-19 | 1 | 2 |
| username1 | 01-APR-19 | 0 | null |
| username1 | 02-APR-19 | 1 | 3 |
| username1 | 02-APR-19 | 1 | 3 |
| username1 | 02-APR-19 | 1 | 3 |
| username1 | 02-APR-19 | 0 | null |
| username1 | 02-APR-19 | 0 | null |