Write a SQL query using window functions to calculate each patient's rolling 7-day average measurement and rank patients within each site.
