SQL subqueries PostgreSQL 12

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

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