Oracle 18c

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

CREATE TABLE myTable (
  ID              INT,
  TicketId        INT,
  Status          VARCHAR2(20),
  Ticket_Created  DATE,
  Status_Changed  TIMESTAMP
);
INSERT ALL
INTO myTable VALUES (1,  1, 'other_error', DATE'2020-01-01', TIMESTAMP'2020-01-01 08:00:00') 
INTO myTable VALUES (2,  2, 'tech_error',  DATE'2020-01-01', TIMESTAMP'2020-01-01 09:00:00') 
INTO myTable VALUES (3,  3, 'unknown',     DATE'2020-01-01', TIMESTAMP'2020-01-01 09:10:00') 
INTO myTable VALUES (4,  4, 'unknown',     DATE'2020-01-01', TIMESTAMP'2020-01-01 09:20:00') 
INTO myTable VALUES (5,  4, 'tech_error',  DATE'2020-01-01', TIMESTAMP'2020-01-02 09:30:00') 
INTO myTable VALUES (6,  1, 'solved',      DATE'2020-01-01', TIMESTAMP'2020-01-02 10:00:00') 
INTO myTable VALUES (7,  2, 'solved',      DATE'2020-01-01', TIMESTAMP'2020-01-02 07:00:00') 
INTO myTable VALUES (8,  5, 'tech_error',  DATE'2020-01-02', TIMESTAMP'2020-01-02 08:00:00') 
INTO myTable VALUES (9,  6, 'unknown',     DATE'2020-01-02', TIMESTAMP'2020-01-02 08:30:00') 
INTO myTable VALUES (10, 6, 'solved',      DATE'2020-01-02', TIMESTAMP'2020-01-02 09:30:00') 
INTO myTable VALUES (11, 5, 'solved',      DATE'2020-01-02', TIMESTAMP'2020-01-03 08:40:00') 
INTO myTable VALUES (12, 4, 'unknown',     DATE'2020-01-01', TIMESTAMP'2020-01-03 09:00:00') 
SELECT 1 FROM DUAL;
12 rows affected

SELECT * FROM myTable


ID
TICKETID
STATUS
TICKET_CREATED
STATUS_CHANGED


1
1
other_error
01-JAN-20
01-JAN-20 08.00.00.000000

2
2
tech_error
01-JAN-20
01-JAN-20 09.00.00.000000

3
3
unknown
01-JAN-20
01-JAN-20 09.10.00.000000

4
4
unknown
01-JAN-20
01-JAN-20 09.20.00.000000

5
4
tech_error
01-JAN-20
02-JAN-20 09.30.00.000000

6
1
solved
01-JAN-20
02-JAN-20 10.00.00.000000

7
2
solved
01-JAN-20
02-JAN-20 07.00.00.000000

8
5
tech_error
02-JAN-20
02-JAN-20 08.00.00.000000

9
6
unknown
02-JAN-20
02-JAN-20 08.30.00.000000

10
6
solved
02-JAN-20
02-JAN-20 09.30.00.000000

11
5
solved
02-JAN-20
03-JAN-20 08.40.00.000000

12
4
unknown
01-JAN-20
03-JAN-20 09.00.00.000000


with cte_ranges as (
  select id, status, ticketid, ticket_created
  , status_changed as started
  , coalesce(
     lead(status_changed) over (partition by ticketid order by status_changed)
    , current_timestamp) as ended
  from myTable
  where trunc(ticket_created) between DATE'2020-01-01' and DATE'2020-01-02'
)
select q.ticket_date   as "Ticket Created"
     , q.total_tickets as "Total Created"
     , q.total_unknown as "Unknown tickets created"
     , endofday.total_unknown "Total tickets in Unknown status"
from
(
  select trunc(t.ticket_created) as ticket_date
  , count(distinct t.ticketid) as total_tickets
  , count(distinct case when t.status = 'unknown' then t.ticketid end) as total_unknown
  from cte_ranges t
  group by trunc(t.ticket_created) 
) q
left join (
  select trunc(cast(dt as date)) as ticket_date
  , count(distinct case when status = 'unknown' then ticketid end) as total_unknown
  from cte_ranges
  join (
    select distinct 
     cast(trunc(ticket_created)+1 as timestamp) - interval '1' second as dt 
    from cte_ranges
  ) cutoff on dt between started and ended
  group by cast(dt as date)
) endofday 
on endofday.ticket_date = q.ticket_date;


Ticket Created
Total Created
Unknown tickets created
Total tickets in Unknown status


01-JAN-20
4
2
2

02-JAN-20
2
1
1

ID	TICKETID	STATUS	TICKET_CREATED	STATUS_CHANGED
1	1	other_error	01-JAN-20	01-JAN-20 08.00.00.000000
2	2	tech_error	01-JAN-20	01-JAN-20 09.00.00.000000
3	3	unknown	01-JAN-20	01-JAN-20 09.10.00.000000
4	4	unknown	01-JAN-20	01-JAN-20 09.20.00.000000
5	4	tech_error	01-JAN-20	02-JAN-20 09.30.00.000000
6	1	solved	01-JAN-20	02-JAN-20 10.00.00.000000
7	2	solved	01-JAN-20	02-JAN-20 07.00.00.000000
8	5	tech_error	02-JAN-20	02-JAN-20 08.00.00.000000
9	6	unknown	02-JAN-20	02-JAN-20 08.30.00.000000
10	6	solved	02-JAN-20	02-JAN-20 09.30.00.000000
11	5	solved	02-JAN-20	03-JAN-20 08.40.00.000000
12	4	unknown	01-JAN-20	03-JAN-20 09.00.00.000000