Get most recent status for each category

Question

I came across this interesting problem. I have a table named email_track to track email status for each category say (invitation, newsletter)

This is how my table data looks,

With these following queries I'm able to get most recent record for each to_email,

with `et2` as (
    select `et1`.`category`, `et1`.`to_email`, `et1`.`subject`, `et1`.`status`, ROW_NUMBER() OVER (partition by `to_email` order by `id` desc) as `rn`
    from `email_track` `et1`
)
select * from `et2` where `rn` = 1;

select `et1`.`category`, `et1`.`to_email`, `et1`.`subject`, `et1`.`status`, `et2`.`id`
from `email_track` `et1`
left join `email_track` `et2` on (`et1`.`to_email` = `et2`.`to_email` and `et1`.`id` < `et2`.`id`)
where `et2`.`id` is null;

What I'm expecting is for email john@example.com I should get two records one for category invitation and the other for the newsletter. Now, we won't get that result since we partition by to_email

Answer 1

I should get two records one for category invitation and the other for the newsletter. Now, we won't get that result since we partition by to_email.

Adding the category to the partition by clause of the window function should be enough to give your the result that you want:

with et2 as (
    select et1.category, et1.to_email, et1.subject, et1.status, 
        row_number() over(partition by to_email, category order by id desc) as rn
    from email_track et1
)
select * from et2 where rn = 1;