I have a device_events table in which I get signals from various devices. These events could be online offline events for a device. Basically I want all the offline and online events for a device to be paired. We can assume that the online event will always be preceded by an offline event.
The table will look something like this:
| trigger | device_id | created_at |
|---|---|---|
| Online | 1 | 2022-01-23 17:39:12.986 |
| Offline | 1 | 2022-01-23 17:38:12.986 |
| Online | 1 | 2022-01-22 16:39:12.986 |
| Offline | 1 | 2022-01-22 16:38:12.986 |
| Online | 2 | 2022-01-21 14:39:12.986 |
| Offline | 2 | 2022-01-21 14:38:12.986 |
| Offline | 3 | 2022-01-20 14:38:12.986 |
I want the following output:
| device_id | device_offline | device_online |
|---|---|---|
| 1 | 2022-01-23 17:38:12.986 | 2022-01-23 17:39:12.986 |
| 1 | 2022-01-22 16:38:12.986 | 2022-01-22 16:39:12.986 |
| 2 | 2022-01-21 14:38:12.986 | 2022-01-21 14:39:12.986 |
| 3 | 2022-01-20 14:38:12.986 | [NULL] |
Any help is highly appreciated. Thanks!
>Solution :
Using a self left join:
select d.device_id, d.created_at, d1.created_at from device_events d
left join device_events d1 on d1.device_id = d.device_id and d1.trigger = 'Online'
and d1.created_at = (select min(d3.created_at) from device_events d3 where d3.trigger = 'Online'
and d3.device_id = d.device_id and d3.created_at >= d.created_at)
where d.trigger = 'Offline'