Write a query to calculate the rolling 7-day average of dropped calls per agent for Aircall.
Use the agents and calls tables. Treat calls with status = 'dropped' as dropped calls and include calendar days with zero dropped calls. Return only dates with a full seven-day lookback window and at least one call for the agent.
agent_id, agent_name, call_date, daily_dropped_calls, and rolling_7_day_average.agent_id, then call_date ascending.| Column | Type | Description |
|---|---|---|
| agent_idPK | INT | Unique Aircall agent identifier |
| agent_name | VARCHAR(100) | Agent display name |
| Column | Type | Description |
|---|---|---|
| call_idPK | INT | Unique call identifier |
| agent_id | INT | Agent assigned to the call |
| call_started_at | TIMESTAMP | Timestamp when the call started |
| status | VARCHAR(20) | Call outcome, such as dropped or answered |