I have this table that tracks swab results. Each user can take multiple swabs on different dates (with precision up to the second) with different results ('Y' -> positive, 'N' -> negative).
What I'm aiming for is counting for each distinct day that exists in the table, the number of distinct users that, up until each date, are still positive.
CREATE TABLE swabs(USER_ID, DATE_RESULT, POSITIVITY) AS
SELECT 1, TIMESTAMP'2023-03-18 23:59:57','Y' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP'2023-03-18 23:59:58','Y' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP'2023-03-18 23:59:59','Y' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP'2023-03-19 23:59:56','N' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP'2023-03-19 23:59:57','N' FROM DUAL UNION ALL
SELECT 4, TIMESTAMP'2023-03-19 23:59:58','Y 'FROM DUAL UNION ALL
SELECT 7,TIMESTAMP'2023-03-19 23:59:59','Y' FROM DUAL UNION ALL
SELECT 5,TIMESTAMP'2023-03-20 23:59:57','Y' FROM DUAL UNION ALL
SELECT 6, TIMESTAMP'2023-03-20 23:59:58','Y' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP'2023-03-20 23:59:59','Y' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP'2023-03-21 23:59:57','N' FROM DUAL UNION ALL
SELECT 4, TIMESTAMP'2023-03-21 23:59:58', 'N' FROM DUAL UNION ALL
SELECT 7, TIMESTAMP '2023-03-21 23:59:59', 'N' FROM DUAL UNION ALL
SELECT 1,TIMESTAMP'2023-03-22 23:59:56', 'Y' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP'2023-03-22 23:59:57','Y' FROM DUAL UNION ALL
SELECT 3,TIMESTAMP'2023-03-22 23:59:58','Y' FROM DUAL UNION ALL
SELECT 4, TIMESTAMP'2023-03-22 23:59:59','Y' FROM DUAL
The result should look like this.
Any help or guidance would be greatly appreciated.
I put together something below as an attempt but I'm obviously on the wrong track.
DATE_DISTINCT | TOTAL_INFECTED |
| ------------- | -------------- |
| 2023-03-18 | 3 |
| 2023-03-19 | 3 |
| 2023-03-20 | 6 |
| 2023-03-21 | 3 |
| 2023-03-22 | 6 |
SELECT COUNT(positivity)
FROM swabs s
INNER JOIN (SELECT user_id,
MAX(date_result) AS max_date
FROM swabs
WHERE date_result < TO_DATE('2023-03-21', 'YYYY-MM-DD')
GROUP BY user_id) m ON s.user_id = m.user_id AND s.date_result = m.max_date
WHERE s.date_result < TO_DATE('2023-03-21', 'YYYY-MM-DD')
AND s.POSITIVITY = 'Y';