You are given daily device activity data from Resmed devices. Write a PostgreSQL query to return, for each region and calendar date, the number of active devices and the rolling 7-day average of active devices. Treat a device as active on a date when it has at least one activity event that day. Your result should include dates with zero active devices, and the 7-day average should be calculated over the current date plus the previous 6 dates within each region.
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity event identifier |
| device_id | INT | Unique device identifier |
| region | VARCHAR(50) | Geographic region assigned to the device |
| activity_ts | TIMESTAMP | Timestamp when the device generated an activity event |
| activity_type | VARCHAR(30) | Type of activity event recorded from the device |