Your question is Aggregate Alerts by Site. 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.
You are given two PostgreSQL tables from a Copeland monitoring workflow: sites and alerts. Write an SQL query that joins the tables and returns one row per site with the total number of alerts and the average alert severity. Include only active sites, and sort the result by total alerts descending, then by site name ascending.
| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique site identifier |
| site_name | VARCHAR(100) | Site name |
| region | VARCHAR(50) | Geographic region for the site |
| is_active | BOOLEAN | Whether the site is active |
| Column | Type | Description |
|---|---|---|
| alert_idPK | INT | Unique alert identifier |
| site_id | INT | Site associated with the alert |
| alert_type | VARCHAR(50) | Type of alert |
| severity | INT | Severity score from 1 to 5 |
| created_at | DATE | Date the alert was created |