Microsoft SQL Server "DENSE LAG()"

Question

I artificiialy inserted to a calls history db a bunch of hypothetical future calls for each user id we have, as you can see in the picture, I hope its clear.

The call_numbered are obviously ordred with dense_rank() function.

Well I'm trying to give for each 'future call' the last 'real' call. Is it possible to do so with some sophisticated syntax of window functions that maybe im not aware of?

I kind of solved it using first_value() inside a case statement but it created a lot of mess in the code, and it doesnt generalize to other cases.

I'm trying to find more elegant solution to reach the 'last real' call value.

Does someone have any idea?

Answer 1

select d.user_id, call_date, status,
    dense_rank() over(partition by user_id order by coalesce(hypo_call_date,call_date)) as call_number
from (
    select d.*, 
        case status when 'Hypothetical' then
            min(case status when 'Hypothetical' then call_date end) over(partition by user_id, status order by call_date) 
        end as hypo_call_date       
    from data d
) d
;

Note that if you use MAX on 'Real Call' then all the "hypo" will have the rank of the latest "real", with MIN on 'Hypo' they have "+1" as in your picture.