By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with table1(tran_id, user_id, start_date, end_date) as (
select 1, 100, date '2018-04-01', date '2018-04-02' from dual union all
select 2, 100, date '2018-06-14', date '2018-06-14' from dual union all
select 4, 100, date '2018-06-12', date '2018-06-12' from dual union all
select 7, 101, date '2018-01-05', date '2018-01-05' from dual union all
select 9, 101, date '2018-01-08', date '2018-01-08' from dual union all
select 3, 101, date '2018-01-03', date '2018-01-03' from dual )
select tran_id, user_id, start_date, end_date, nvl(nsd - start_date, 0) diff
from (
select t.*, lead(start_date) over (partition by user_id
order by start_date) nsd
from table1 t)
| TRAN_ID | USER_ID | START_DATE | END_DATE | DIFF |
|---|---|---|---|---|
| 1 | 100 | 01-APR-18 | 02-APR-18 | 72 |
| 4 | 100 | 12-JUN-18 | 12-JUN-18 | 2 |
| 2 | 100 | 14-JUN-18 | 14-JUN-18 | 0 |
| 3 | 101 | 03-JAN-18 | 03-JAN-18 | 2 |
| 7 | 101 | 05-JAN-18 | 05-JAN-18 | 3 |
| 9 | 101 | 08-JAN-18 | 08-JAN-18 | 0 |