Rolling Transaction SQL and Ranking

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

1. One row per user and day with at least one transaction
2. Columns: user_id, activity_date, daily_total_spend, rolling_7_day_avg_transaction_amount, and daily_spend_rank
3. 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.