Write a SQL query using window functions to compute a rolling 7-day average and rank entities within each group.
