SQL Rolling Average and Ranking

Loft wants to compare user activity across regions using transaction volume. Write a PostgreSQL query that calculates each user's seven-day rolling average of completed transactions and ranks users within their region as of January 10, 2024.

Requirements

1. Consider the seven calendar days from January 4 through January 10, including days with zero transactions.
2. Count only transactions whose status is completed; exclude pending and cancelled transactions.
3. Calculate each user's average daily completed transaction count across the seven-day window.
4. Rank users within each region by descending rolling average using a window function. Tied averages must receive the same rank.
5. Include users with no completed transactions and sort the final output by region, rank, and user ID.

Output details

• Return exactly region, user_id, user_name, report_date, rolling_avg, and region_rank, in that order.
• Round rolling_avg to four decimal places.
• Assign tied averages the same rank while leaving gaps after ties.
• Sort ascending by region, region_rank, and user_id, placing NULL regions last.