Your question is Top Clinics by Device Offline Rate. 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 clinic-level device status events and need to identify the clinics with the worst reliability over the last 30 days. Write a PostgreSQL query that returns the top 10% of clinics by device offline rate, where offline rate is defined as offline events divided by total device status events in the 30-day window.
| Column | Type | Description |
|---|---|---|
| clinic_idPK | INT | Primary key for the clinic |
| clinic_name | VARCHAR(255) | Human-readable clinic name |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Primary key for the device event |
| clinic_id | INT | Clinic associated with the event |
| device_id | INT | Device identifier |
| event_ts | TIMESTAMP | Timestamp when the device status event occurred |
| event_type | VARCHAR(50) | Device status event type |