Query to find data where value of data changed to 0

Question

I am trying to write a query to find the rows where value of particular column changes to 0 from some other value say 1 or above. The value may change from 0 to other value again. But we need data for only the rows where it was some value and where it changed to 0.

Suppose we have a table with below data:

user_id	Event_time	Value
1	Dec 23 2021 8:04AM	1
1	Dec 23 2021 8:05AM	5
1	Dec 23 2021 8:06AM	0
1	Dec 23 2021 9:00AM	1
2	Dec 23 2021 8:05AM	0
2	Dec 23 2021 9:06AM	7
2	Dec 23 2021 10:04AM	1
3	Dec 23 2021 8:05AM	0
4	Dec 23 2021 9:06AM	1
4	Dec 23 2021 10:04AM	0

So the output should be like this:

user_id	Event_time	Value
1	Dec 23 2021 8:05AM	5
1	Dec 23 2021 8:06AM	0
4	Dec 23 2021 9:06AM	1
4	Dec 23 2021 10:04AM	0

In short i need data for the rows when value is 1 and changed to 0. I have tried to write a script for above but the data is not entirely correct. Below is the script:

CREATE TABLE #temp(
       [user_id] [varchar](100) NULL,
       [event_time] [varchar](100) NULL,
             [value] [varchar](100) NULL
)
;with CTE as(
select a.user_id,a.event_time,a.value from events as a  where  a.user_id in
(
select distinct b.user_id from events b where   b.value=0) 
)

insert into #temp 
select * from CTE where user_id in(select distinct user_id from CTE where value<>0)

;WITH CTE1 AS (
    select a.*,ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY a.event_time DESC) AS rn from #temp a inner join #temp b on
a.user_id=b.user_id
where a.event_time<b.event_time and a.value<>0 and b.value=0
)
SELECT * FROM CTE1 WHERE rn = 1;
drop table #temp

Can anyone check and help correct the query?

Answer 1

WITH cte AS (
    SELECT user_id, event_time, value,
           LAG(value) OVER (PARTITION BY user_id ORDER BY event_time) lag_value,
           LEAD(value) OVER (PARTITION BY user_id ORDER BY event_time) lead_value
    FROM source_table 
)
SELECT user_id, event_time, value
FROM cte
WHERE (value = 0 AND lag_value <> 0)
   OR (value <> 0 AND lead_value = 0)
ORDER BY 1, 2