SQL Rolling Average and Ranking | Dataford Interview Questions

Your question is SQL Rolling Average and Ranking. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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

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

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Loft user identifier
user_name	VARCHAR(100)	User's display name
region	VARCHAR(50)	User's operating region

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
user_id	INTEGER	User associated with the transaction
transaction_date	DATE	Date the transaction occurred
status	VARCHAR(20)	Transaction processing status