Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE swabs ( ID, USER_ID, DATE_RESULT, POSITIVITY ) AS
SELECT  1, 1, DATE '2023-03-18' + INTERVAL '23:59:57' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  2, 2, DATE '2023-03-18' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  3, 3, DATE '2023-03-18' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  4, 2, DATE '2023-03-19' + INTERVAL '23:59:56' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT  5, 3, DATE '2023-03-19' + INTERVAL '23:59:57' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT  6, 4, DATE '2023-03-19' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  7, 7, DATE '2023-03-19' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  8, 5, DATE '2023-03-20' + INTERVAL '23:59:57' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  9, 6, DATE '2023-03-20' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 10, 2, DATE '2023-03-20' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 11, 1, DATE '2023-03-21' + INTERVAL '23:59:57' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT 12, 4, DATE '2023-03-21' + INTERVAL '23:59:58' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT 13, 7, DATE '2023-03-21' + INTERVAL '23:59:59' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT 14, 1, DATE '2023-03-22' + INTERVAL '23:59:56' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 15, 2, DATE '2023-03-22' + INTERVAL '23:59:57' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 16, 3, DATE '2023-03-22' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 17, 4, DATE '2023-03-22' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL;
17 rows affected

WITH dates (day, user_id, positivity) AS (
  SELECT TRUNC(date_result),
         user_id,
         MAX(positivity) KEEP (DENSE_RANK LAST ORDER BY date_result)
  FROM   swabs
  GROUP BY
         TRUNC(date_result),
         user_id
),
date_range (start_day, end_day) AS (
  SELECT MIN(day), MAX(day)
  FROM   dates
),
calendar (day) AS (
  SELECT start_day + LEVEL - 1
  FROM   date_range
  CONNECT BY start_day + LEVEL - 1 <= end_day
),
results (day, user_id, positivity) AS (
  SELECT c.day,
         d.user_id,
         LAST_VALUE(d.positivity IGNORE NULLS)
           OVER (PARTITION BY d.user_id ORDER BY c.day)
  FROM   calendar c
         LEFT OUTER JOIN dates d
         PARTITION BY (d.user_id)
         ON (c.day = d.day)
)
SELECT day,
       COUNT(CASE positivity WHEN 'Y' THEN 1 END) AS num_positive
FROM   results
GROUP BY day
ORDER BY day


DAY
NUM_POSITIVE


2023-03-18 00:00:00
3

2023-03-19 00:00:00
3

2023-03-20 00:00:00
6

2023-03-21 00:00:00
3

2023-03-22 00:00:00
6
DAY	NUM_POSITIVE
2023-03-18 00:00:00	3
2023-03-19 00:00:00	3
2023-03-20 00:00:00	6
2023-03-21 00:00:00	3
2023-03-22 00:00:00	6