How do you design a SQL query using window functions to calculate a rolling 7-day average and rank entities within each group?
