Rolling Average Transactions SQL

Airtel Payments Bank wants to monitor short-term transaction activity for active users. Write a PostgreSQL query that calculates a 7-day rolling average of each user's daily transaction count.

The rolling window should include the current transaction date and the preceding six calendar days. Average only the dates present in the daily transaction data, rather than treating missing dates as zero-transaction days.

Requirements

1. Include only users whose account_status is active.
2. Aggregate multiple transactions for the same user and date into one daily_transactions value.
3. Use a window function partitioned by user and ordered by transaction date.
4. Return user_id, full_name, transaction_date, daily_transactions, and rolling_7_day_average, ordered by user and date.