SQL Rolling Average by Asset Class

Point72's trading analytics team wants to monitor short-term changes in transaction volume across its market data. Write a PostgreSQL query that calculates a seven-day moving average for each ticker, while keeping asset classes separate.

Requirements

1. Aggregate transaction volume by asset_class, ticker, and trade_date before applying the window function.
2. Calculate the moving average using the current date and the preceding six calendar days. Include only dates present in the daily aggregate; do not create missing calendar dates.
3. Partition the window by both asset_class and ticker, and order each partition chronologically by trade_date.
4. Return the asset class, ticker, trade date, daily volume, and moving average, ordered by asset class, ticker, and trade date.

A NULL volume should be ignored by SUM and AVG, consistent with PostgreSQL aggregate behavior.