Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table orders(idcustomer int, orderDate date);

insert into orders values (1, TO_DATE('22/April/2011 8:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

insert into orders values (1, TO_DATE('22/April/2011 9:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

insert into orders values (1, TO_DATE('23/April/2011 9:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

insert into orders values (1, TO_DATE('24/April/2011 8:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

insert into orders values (1, TO_DATE('26/April/2011 8:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

insert into orders values (2, TO_DATE('22/April/2011 8:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

insert into orders values (2, TO_DATE('23/April/2011 9:30:00AM','DD/MON/YY HH:MI:SSAM'));
1 rows affected

WITH truncatedQueries as (
select idcustomer, TRUNC(orderDate) as orderDate from orders 
)


select  idcustomer,  TO_CHAR(orderDate, 'dd.mm.yyyy') from truncatedQueries


IDCUSTOMER
TO_CHAR(ORDERDATE,'DD.MM.YYYY')


1
22.04.2011

1
22.04.2011

1
23.04.2011

1
24.04.2011

1
26.04.2011

2
22.04.2011

2
23.04.2011


WITH truncatedQueries as (
select distinct idcustomer, TRUNC(orderDate) as orderDate from orders 
)

select a.idcustomer,a.orderDate, b.orderDate, b.orderDate-(a.orderDate)  from truncatedQueries a inner join 
truncatedQueries b on 
a.idcustomer = b.idcustomer AND a.orderDate < b.orderDate


IDCUSTOMER
ORDERDATE
ORDERDATE
B.ORDERDATE-(A.ORDERDATE)


1
22-APR-11
23-APR-11
1

1
23-APR-11
26-APR-11
3

1
24-APR-11
26-APR-11
2

1
22-APR-11
26-APR-11
4

1
23-APR-11
24-APR-11
1

1
22-APR-11
24-APR-11
2

2
22-APR-11
23-APR-11
1


WITH truncatedQueries as (
select distinct idcustomer, TRUNC(orderDate) as orderDate from orders 
)

select a.idcustomer,a.orderDate, b.orderDate, b.orderDate-(a.orderDate)  from truncatedQueries a inner join 
truncatedQueries b on 
a.idcustomer = b.idcustomer AND (b.orderDate)-(a.orderDate) = 1


IDCUSTOMER
ORDERDATE
ORDERDATE
B.ORDERDATE-(A.ORDERDATE)


1
22-APR-11
23-APR-11
1

1
23-APR-11
24-APR-11
1

2
22-APR-11
23-APR-11
1


WITH truncatedQueries as (
select distinct idcustomer, TRUNC(orderDate) as orderDate from orders 
)

select * from

(select a.idcustomer,a.orderDate as orderDart, b.orderDate, b.orderDate-a.orderDate  from truncatedQueries a inner join 
truncatedQueries b on 
a.idcustomer = b.idcustomer AND a.orderDate < b.orderDate  AND
 b.orderDate-a.orderDate =1)temp;


IDCUSTOMER
ORDERDART
ORDERDATE
B.ORDERDATE-A.ORDERDATE


1
22-APR-11
23-APR-11
1

1
23-APR-11
24-APR-11
1

2
22-APR-11
23-APR-11
1


WITH truncatedQueries as (
select distinct idcustomer, TRUNC(orderDate) as orderDate from orders 
),
rawData as (
select a.idcustomer,a.orderDate as order1, b.orderDate as order2, b.orderDate-a.orderDate  from truncatedQueries a inner join 
truncatedQueries b on 
a.idcustomer = b.idcustomer AND a.orderDate < b.orderDate  AND
 b.orderDate-a.orderDate =1
),
intervals as (
select idCustomer as CustomerId, min(order1) as StartDate, max(order2) as EndDate from rawData
group by idCustomer order by idCustomer
)
select CustomerId,StartDate, EndDate, endDate - StartDate as numDays  from intervals;


CUSTOMERID
STARTDATE
ENDDATE
NUMDAYS


1
22-APR-11
24-APR-11
2

2
22-APR-11
23-APR-11
1

IDCUSTOMER	TO_CHAR(ORDERDATE,'DD.MM.YYYY')
1	22.04.2011
1	22.04.2011
1	23.04.2011
1	24.04.2011
1	26.04.2011
2	22.04.2011
2	23.04.2011

IDCUSTOMER	ORDERDATE	ORDERDATE	B.ORDERDATE-(A.ORDERDATE)
1	22-APR-11	23-APR-11	1
1	23-APR-11	26-APR-11	3
1	24-APR-11	26-APR-11	2
1	22-APR-11	26-APR-11	4
1	23-APR-11	24-APR-11	1
1	22-APR-11	24-APR-11	2
2	22-APR-11	23-APR-11	1