Your question is Detect Anomalous Daily Device Pings. 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 ping counts from Athelas device telemetry. Write a PostgreSQL query that flags anomalous days per device by comparing each day to the previous available day and by detecting gaps in the daily sequence. Return one row per device-day with the current count, the previous count, the day-over-day change, a gap flag, and an anomaly label. Treat a day as anomalous when the ping count changes by more than 50% versus the previous available day, or when there is a gap of more than 1 day between consecutive records for the same device. Keep the result ordered by device and date.
| Column | Type | Description |
|---|---|---|
| device_id | INT | Device identifier |
| ping_date | DATE | Calendar date of the daily ping record |
| ping_count | INT | Number of pings recorded that day |
| firmware_version | VARCHAR(20) | Firmware version reported that day |
| region | VARCHAR(50) | Device region; may be NULL |