By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select 1 as id, 2447 as book_id, 274761 as client_id, 1 as type, '2020-04-07 09:38:54' as dte from dual union all
select 2 as id, 2447 as book_id, 274761 as client_id, 2 as type, '2020-04-07 09:39:25' as dte from dual union all
select 3 as id, 2447 as book_id, 274761 as client_id, 1 as type, '2020-04-07 09:39:53' as dte from dual union all
select 4 as id, 2447 as book_id, 274761 as client_id, 2 as type, '2020-04-07 09:41:03' as dte from dual union all
select 5 as id, 1000 as book_id, 274761 as client_id, 1 as type, '2020-04-07 09:52:05' as dte from dual union all
select 6 as id, 2447 as book_id, 274761 as client_id, 1 as type, '2020-04-07 10:04:54' as dte from dual union all
select 7 as id, 1000 as book_id, 274761 as client_id, 2 as type, '2020-04-07 10:05:38' as dte from dual union all
select 8 as id, 2447 as book_id, 274761 as client_id, 2 as type, '2020-04-07 10:06:04' as dte from dual union all
select 9 as id, 3002 as book_id, 274761 as client_id, 1 as type, '2020-04-07 11:22:02' as dte from dual
9 rows affected
select rownum as id, book_id, client_id, dte as in_date, out_date
from (select t.*,
lead(dte) over (partition by book_id, client_id order by dte) as out_date
from t
) t
where type = 1;
| ID | BOOK_ID | CLIENT_ID | IN_DATE | OUT_DATE |
|---|---|---|---|---|
| 1 | 1000 | 274761 | 2020-04-07 09:52:05 | 2020-04-07 10:05:38 |
| 2 | 2447 | 274761 | 2020-04-07 09:38:54 | 2020-04-07 09:39:25 |
| 3 | 2447 | 274761 | 2020-04-07 09:39:53 | 2020-04-07 09:41:03 |
| 4 | 2447 | 274761 | 2020-04-07 10:04:54 | 2020-04-07 10:06:04 |
| 5 | 3002 | 274761 | 2020-04-07 11:22:02 | null |