Your question is Rolling Active Usage and Anomalies. 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.
Given a large table of device diagnostic logs, write a query to compute rolling 30-day active usage rates and flag anomalies.
Treat a device as active on a date when it has at least one log with positive usage minutes. The rate is the number of active days in the inclusive trailing 30-day period divided by 30. Exclude logs outside a device's active lifetime and flag rates outside the device's expected range.
device_id, observation_date, active_usage_rate, and anomaly_flag.device_id, then observation_date.TRUE for anomaly_flag when the rate is below the minimum or above the maximum expected rate.| Column | Type | Description |
|---|---|---|
| device_idPK | INT | Unique device identifier |
| product_model | VARCHAR(100) | Apple device model |
| activated_on | DATE | Date the device became active |
| retired_on | DATE | Date the device was retired |
| minimum_expected_rate | NUMERIC(5,4) | Minimum expected 30-day active usage rate |
| maximum_expected_rate | NUMERIC(5,4) | Maximum expected 30-day active usage rate |
| Column | Type | Description |
|---|---|---|
| log_idPK | INT | Unique diagnostic log identifier |
| device_id | INT | Referenced device identifier |
| log_date | DATE | Date the diagnostic log was recorded |
| usage_minutes | INT | Usage minutes recorded by the diagnostic log |
| event_type | VARCHAR(50) | Diagnostic event classification |