I am trying to write a query to track the changes of a boolean column in a table. The table looks like this
ClientHistory
-------------------------------------------------------------
| id | client_id | client_name | is_active | Timestamp |
-------------------------------------------------------------
| 1 | 1 |Example Client| True | 06/15/2020|
-------------------------------------------------------------
| 2 | 1 |Client Change | True | 06/16/2020|
-------------------------------------------------------------
| 3 | 1 |Client Change | False | 06/17/2020 |
So what i would want is row 3 where the is_active changed to false. Then after that i would want the next row where it changed to true again.
This is what i tried:
SELECT a.*
FROM client_history AS a
WHERE a.is_active <>
( SELECT b.is_active
FROM client_history AS b
WHERE a.client_id = b.client_id
AND a.timestamp > b.timestamp
ORDER BY b.timestamp DESC
LIMIT 1
)
So the subquery is trying to get the previous row of the same client_id by getting the most recent timestamp before it . Then in query check if is_active does not equal the is_active from the previous row. But this is not working as planned. I expect as I trigger acttive/inactive it should be alternating in this query but it is not. Anybody got any tips?
Use window functions!
select ch.*
from (select ch.*,
lag(is_active) over (partition by client_id order by timestamp) as prev_is_active
from client_history ch
) ch
where is_active <> prev_is_active;