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 |