Return both the largest and second largest dates, in separate columns, grouped by some factor

Been struggling with this. I need to return the largest date, grouped by some factor in 1 column, and the second largest date in a separate column. I’m unable to use DATEADD function since there are gaps in the date. The desired output will have three columns (the factor being grouped on, max date, 2nd largest date) and many rows. Thank you.

Answer

The generic method is to use row_number() and aggregation. Such as this:

select factor, max(date), min(date)
from (select t.*, row_number() over (partition by factor order by date) as seqnum
      from t
     ) t
where seqnum <= 2
group by factor;

In the event of duplicates, this will return the same date twice. If you want different dates, then use dense_rank() instead of row_number().