Detect Fridge Outages
0:00:00
Daikin smart fridges log a consumption event every time a user removes food from the refrigerator. The engineering team suspects some devices may be going offline and wants to flag long inactivity periods.
Using the fridge_events table, write a query to detect cases where a device goes 48 hours or more between two consecutive events.
For each qualifying gap, return:
device_idgap_start: timestamp of the earlier eventgap_end: timestamp of the later eventgap_hours: whole hours between the two timestamps (rounded down)
Note: Sort the results by device_id and gap_start in ascending order.
Schema
Input:
fridge_events table
| Column | Type |
|---|---|
| device_id | INTEGER |
| event_time | DATETIME |
| item | VARCHAR |
Output:
| Column | Type |
|---|---|
| device_id | INTEGER |
| gap_start | DATETIME |
| gap_end | DATETIME |
| gap_hours | INTEGER |
Example
Input:
fridge_events table
| device_id | event_time | item |
|---|---|---|
| 1 | 2025-01-01 08:00:00 | milk |
| 1 | 2025-01-01 20:00:00 | yogurt |
| 1 | 2025-01-03 21:00:00 | eggs |
| 1 | 2025-01-06 09:00:00 | juice |
| 2 | 2025-01-02 10:00:00 | soda |
| 2 | 2025-01-05 11:00:00 | cheese |
Output:
| device_id | gap_start | gap_end | gap_hours |
|---|---|---|---|
| 1 | 2025-01-01 20:00:00 | 2025-01-03 21:00:00 | 49 |
| 1 | 2025-01-03 21:00:00 | 2025-01-06 09:00:00 | 60 |
| 2 | 2025-01-02 10:00:00 | 2025-01-05 11:00:00 | 73 |
Explanation:
To solve this problem, we need to compare each consumption event with the next event for the same device to determine whether a long inactivity gap exists. The key step is identifying consecutive events within each device’s event history.
Device 1
Events:
- 2025-01-01 20:00 → 2025-01-03 21:00
- 2025-01-03 21:00 → 2025-01-06 09:00
Gap 1:
- Time between events: 49 hours → qualifies (≥ 48 hours)
Gap 2:
- Time between events: 60 hours → qualifies (≥ 48 hours)
Device 2
Events:
- 2025-01-02 10:00 → 2025-01-05 11:00
Gap 1:
- Time between events: 73 hours → qualifies (≥ 48 hours)
.
.
.
.
Comments