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 |