Query to find data where value of data changed to 0

Posted on

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 :

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

Leave a Reply

Your email address will not be published.