Which window sql function will capture change-CodePudding

I have a user table that records the user state for every day.

I'm trying to use a window function that captures all the max dates where they were active before they changed states.

So for example, this user was last active on 1/2/2022 before they switched states to deleted.

What I'm trying to achieve is the results in the green column.

However, I'm not sure I can do this with a single windows function.

I've tried row_number, dense_rank, rank , creating two window functions on top of each other.

Any ideas?




with base as (

SELECT
1            AS USER_ID
,'2022-01-01' AS DATE
,'Active'     AS STATE

UNION ALL 

SELECT
1            AS USER_ID
,'2022-01-02' AS DATE
,'Active'    AS STATE    
  
UNION ALL  
    
SELECT
1            AS USER_ID
,'2022-01-03' AS DATE
,'Deleted'    AS STATE

UNION ALL 

SELECT
1            AS USER_ID
,'2022-01-04' AS DATE
,'Active'    AS STATE

UNION ALL 

SELECT
1            AS USER_ID
,'2022-01-05' AS DATE
,'mute'    AS STATE


)



/*FIND ALL STATE CHANGES FOR EACH USER */

SELECT 
dense_rank() OVER (PARTITION BY USER_ID, STATE  ORDER BY DATE DESC) STATE_CHANGE_FLAG
,*
FROM BASE
ORDER BY  DATE DESC

CodePudding user response：

with transitions as (
    select *,
        case when STATE = 'Active'
              and lag(STATE) over (partition by USER_ID order by DT) <> 'Active'
             then 1 end as flag
    from T
), groups as (
    select *, count(flag) over (partition by USER_ID order by DT) as grp
    from transitions
)
select *,
    count(case when STATE = 'Active' then 1 end)
        over (partition by USER_ID, grp) -
    count(case when STATE = 'Active' then 1 end)
        over (partition by USER_ID, grp order by DT)   1 as X
from groups
order by USER_ID, DT desc;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cd1cb9aeea01d80605a14a285706e560

This does work but I feel there should be a slightly cleaner way still...