Your question is SQL Moving Average With Windows. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
customers to transactions and exclude transactions without a matching customer.daily_amount.| Column | Type | Description |
|---|---|---|
| customer_idPK | INTEGER | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer display name |
| segment | VARCHAR(30) | Customer segment |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction identifier |
| customer_id | INTEGER | References customers.customer_id |
| transaction_date | DATE | Date of the transaction |
| amount | NUMERIC(12,2) | Transaction amount |