Select max date per year

Question

I have the table as follows:

user_id  date
1        2020-11-15
1        2020-10-15
1        2020-09-15
1        2019-12-15
1        2019-11-15
2        2020-11-15
2        2020-10-15
2        2019-12-15
3        2020-10-15
3        2020-09-15

And I'd like to select the max date for every year per user, so the result would be like:

user_id  date
1        2020-11-15
1        2019-12-15
2        2020-11-15
2        2019-12-15
3        2020-10-15

Some help? Thank you!

Answer 1

Just use aggregation:

select user_id, max(date)
from t
group by user_id, date_trunc('year', date);

If you have more columns that you want, then use distinct on:

select distinct on (user_id, date_trunc('year', date)) t.*
from t
order by user_id, date_trunc('year', date), date desc;