How to select the most recent date for a specific condition in another column in Postgres?

I have a table with a structure like so.

id|subject|date
1 | email | june 1
1 | call  | may 29
1 | call  | may 15
2 | call  | may 17
2 | call  | may 20

I’m trying to create a column with a query called most recent call which has the most recent call date for each id. Like this:

id|most_recent_call
1 |may 29
2 |may 20

I just want to have the most recent date for each id out of all the rows where the subject column equals call

Answer

You can just use conditional aggregation:

select id, max(date) filter(where subject = 'call') most_recent_call
from mytable
group by id

This, of course, assumes that column date is of a date-like datatype (as it should be).