Consecutive weeks a title is in Top 10

I want to replicate the NF week in Top 10 piece. I've managed to create a query which has the WoW rank of the titles, like this

Title	Viewers	Rank	Week
A	3	1	12/31
A	2	1	12/31
B	1	2	12/24

The raw data that I have looks like this:

Title	User_id	Watch_time	Date
A	1	50	2023-12-22
B	2	70	2023-12-22
A	3	30	2023-12-22
A	5	20	2023-12-27
A	6	60	2023-12-27
A	7	50	2023-12-27

I've simply used the rank() function to get this data. Now, I'm trying to track the number of consecutive weeks the title has been in Top 10. Can anyone help me out with this.

This is the code that I'm using to rank the titles

rnk_data as (
    select title, 
       viewers,
       hours_watched,
       row_number() over (partition by week order by viewers desc) title_rank,
       week           
   from raw_data
)

The final output that I'm trying to get is

Title	Weeks_in_top_10	Viewers
A	2	5
B	1	1

Solution

This is based on the pre-aggregated data at the top of the post:

with dataX as (
    -- each times there's a break in the top 10 you want to reset
    select *,
        count(case when "Rank" > 10 then 1 end) over (
            partition by Title order by Week) as grp
    from T
)
select Title, Week,
   -- within each grp keep a running tally of the week counter
   count(*) over (partition by Title, grp order by Week) - 1 as ConsecutiveTop10Weeks
from dataX;