SQL subqueries PostgreSQL 12

Question

I'm having a table similar to this:

first	last	date	pos
john	doe	18-03-2021
harris	potter		10-06-2021
john	doe	10-05-2021
harris	potter	14-06-2021
jessica	potter	14-06-2021
kermit	foster

The use case is as follow:

• The pos column correspond to a positive covid test
• The date column correspond to the vaccination date

To be elligible for a covid certificate, some one must either:

• Been tested positive and have got 1 vaccine
• Have receive 2 vaccine

I'm trying to write a query that return me: totalDose, totalRequieredDose

For exemple:

• If he has tested positive, the totalRequiredDose is 1 and if he has got 1 vaccine, he is elligible. As such, for Harry Potter, totalDoses=1 and totalRequieredDoses=1 and is elligible
• If he has not been tested positive, the totalRequiredDose is 2 and if he has got 2 vaccines, he is elligible. As such, for John Doe, totalDoses=2 and totalRequieredDoses=2 and is elligible

first	last	totalDoses	totalRequieredDoses
john	doe	2	2
harris	potter	1	1
jessica	potter	1	2
kermit	foster	0	2

As Jessica Potter have a vaccine and no pos date, she must have 2 vaccines. So the value 1/2 And Kermit foster have no pos value, he is 0/2 Etc.

I'm scratching my head to write a query (or pl/sql) that could return me such table.

Could someone give me some hints ?

Answer 1

We can aggregate by first and last name. The total doses is simply the count of non NULL vaccination dates. For the total required number of doses, we can start with a value of 2. This value can then be offset by 1 assuming there exists a non NULL date for the pos column, indicating that a given person tested positive at some point.

SELECT
    first,
    last,
    COUNT(date) AS totalDoses,
    2 - (COUNT(*) FILTER (WHERE pos IS NOT NULL) > 0)::int AS totalRequieredDoses
FROM yourTable
GROUP BY
    first,
    last
ORDER BY
    COUNT(date) DESC,
    first,
    last;

Demo