the edge cases are exactly where people trip up bc they dont account for
null values or ties in window functions. i always suggest using
row_number()
instead of
rank()
when you need strict deduplication to avoid
duplicate rows appearing in your final output . it forces you to define a tie-breaking logic which is the real test of the underlying business requirement.