Вычислить для каждой строки, сколько дней подряд к этому моменту пользователь заходил в приложение

USE Useractivity
SELECT UserActivity.Date, UserActivity.UserId, COUNT(Date) AS DaysInRow
FROM UserActivity 
GROUP BY UserActivity.UserId, UserActivity.Date
ORDER BY COUNT(Date)DESC

declare @Test table (uid int, day int);
insert @Test (uid,day) values 
  (1,2),(1,3),(1,3),(1,5),(1,6),(1,8),(1,12),(1,13),(1,14),
  (2,5),(2,5),(2,5),(2,6),(2,7),(2,9),(2,9),(2,13),(2,14);
 
;with CTE as (
    select t.uid, t.day, 1 n
    from @Test t
  union all
  select t.uid, t.day, 1+n
    from CTE r
    join @Test t on t.uid=r.uid and t.day=r.day+1
  )
select * 
from (
  select top(1) with ties c.uid, c.day, c.n
  from CTE c
  order by 
    row_number()
    over(partition by c.uid, c.day order by c.n desc)
  ) r
order by r.uid, r.day