SQL Moving Average With Windows

J.P. Morgan Chase wants a transaction trend view for customer analytics. Write a PostgreSQL query that calculates a 30-day moving average of each customer’s daily transaction total.

Use the current transaction date and the preceding 29 calendar days, so the window covers 30 calendar days in total. Average only days represented in the aggregated transaction data, and ignore NULL transaction amounts through PostgreSQL aggregate behavior.

Requirements

1. Join customers to transactions and exclude transactions without a matching customer.
2. Aggregate multiple transactions for the same customer and date into one daily_amount.
3. Calculate the customer-level moving average with a window function using a 30-day calendar frame.
4. Return results ordered by customer ID and transaction date.

Output details

• Return customer_id, customer_name, transaction_date, daily_amount, and moving_30_day_avg in that exact order.
• Round moving_30_day_avg to 2 decimal places.
• Exclude rows whose transaction_date is NULL.