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.
Output
- One row per user and day with at least one transaction
- Columns:
user_id, activity_date, daily_total_spend, rolling_7_day_avg_transaction_amount, and daily_spend_rank
- Sort by
activity_date, daily_spend_rank, then user_id
Output details
- Sort
activity_date, daily_spend_rank, and user_id in ascending order.
- Assign equal
daily_total_spend values the same rank, leaving gaps after ties.
- Round
rolling_7_day_avg_transaction_amount to two decimal places.
- Compute the rolling average using total transaction amounts divided by transaction counts, not an average of daily averages.
- Treat NULL transaction amounts as contributing zero to
daily_total_spend.
