How would you use SQL window functions to calculate rolling metrics and rank records within each group?