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