Unique records quarter over quarter

Question

I have a table like so -

patient_id   date            org
I5434        2020-06-05      Methodist Hospital
ertw4        2020-08-03      New York Hospital
ouiku35      2020-08-30      Tristar 
ouiku35      2020-01-05      Tristar
ouiku35      2020-04-22      Tristar

The output I am needing is

quarter   count
2020Q1    1
2020Q2    1 
2020Q3    1

3rd record in the table above is not counted because it is the same patient in prior quarters at the same hospital

user_1863229 · Answer

We can try using a calendar table approach here:

WITH quarters AS (
    SELECT 2020 AS year, 1 AS quarter, '2020Q1' AS label UNION ALL
    SELECT 2020, 2, '2020Q2' UNION ALL
    SELECT 2020, 3, '2020Q3' UNION ALL
    SELECT 2020, 4, '2020Q4'
)

SELECT q.label, COUNT(t.date) AS count
FROM quarters q
LEFT JOIN yourTable t
    ON EXTRACT(year FROM t.date) = q.year AND
       EXTRACT(quarter FROM t.date) = q.quarter
GROUP BY q.label
ORDER BY q.label;

Unique records quarter over quarter

1 Answers

Demo