SQL get the last time number was set to 1

Question

I have a sql table similar to the below. I would like to get the last time it was changed from 0 to 1 and the last time it changed back to 0 (highlighted below for an example id).

What I have tried is:

select * from Table t1 join Table t2 on t1.id = t2.id join Table t3 on t1.id = t3.id 
where t1.flag = 1 and t2.flag = 0 and t3.flag 
group by t1.id
having min(t1.createdtime) between max(t2.createdtime) and min(t3.createdtime)

Answer 1

For this dataset, you could use lag() to bring in the flag of the previous row, use it as a filter condition, and then aggregate:

select 
    id,
    max(createdtime) createdtime,
    flag
from (
    select 
        t.*,
        lag(flag) over(partition by id order by createdtime) lagflag
    from mytable t
) t
where (flag = 0 and lagflag = 1) or (flag = 1 and lagflag = 0)
group by id, flag