Limit LAG function to a subset

Question

I'm having a difficult time wrapping my head around this issue. I need to get the previous status of our finished customers. I learned about the LAG function and it almost does what I need, but the issue is it can potentially get the status from a different customer because the previous row is a different CustomerId.

Is there a way to limit the LAG function to only return a value of null if the previous CustomerId is different or something that will generate the same affect? I created some dummy data here: Rexter Lag Testing

As you can see, the 4th row is for customerId 2, but PreviousStatus value is from CustomerId 1. It should be NULL.

Answer 1

SELECT cs.CustomerId 
     , s.StatusName
     , LAG(s.StatusName) OVER (PARTITION BY cs.CustomerId 
                               ORDER BY cs.CustomerStatusId) as PreviousStatus
FROM #CustomerStatus as cs
left join #Status as s on s.StatusId = cs.StatusId

fiddle