Rolling 30-Day Transaction Average

Scotiabank's customer analytics team wants to monitor recent transaction behaviour. Write a PostgreSQL query that calculates a rolling 30-day average transaction amount for each active customer.

Use the current transaction date plus the preceding 29 calendar days, and calculate the average across non-null transactions in that window.

Requirements

1. Join transactions to customers and include only active customers.
2. Partition the window calculation by customer and order it by transaction date.
3. Return each transaction with the customer's ID, name, date, amount, and rolling 30-day average, rounded to two decimal places.
4. Sort the final results by customer ID and transaction date.

Output details

• Return columns named customer_id, customer_name, transaction_date, amount, and rolling_30_day_avg, in that order.
• Sort customer_id, transaction_date, and tied transaction_id values in ascending order.
• Include zero-valued amounts when calculating averages.