Your question is Rolling Transaction SQL 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.
Write an SQL query using window functions to calculate each user's 7-day rolling average transaction amount and rank users within each day by total spend. Use the users and transactions tables. Include users with transactions in the output and calculate the rolling average over calendar days, including the current day.
user_id, activity_date, daily_total_spend, rolling_7_day_avg_transaction_amount, and daily_spend_rankactivity_date, daily_spend_rank, then user_id| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| display_name | VARCHAR(100) | User display name |
| VARCHAR(255) | User email address |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| user_id | INT | User who made the transaction |
| transaction_date | DATE | Calendar date of the transaction |
| amount | NUMERIC(12,2) | Transaction amount |