Your question is Rolling Device Activity by Region. Start with the requirements and the one table 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.
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 |