The Merchant’s Marathon :Identify Product Categories with the Longest Streaks of Consecutive Weeks of Strong Sales Advanced

          with WeeklyCategoryActivity as
  (select p.Category,
          DATE_TRUNC('week', o.ordertimestamp)::date as WeekStart,
          COUNT(distinct o.OrderID) as weekly_sales
   from Orders o
   join OrderDetails od on o.OrderID = od.OrderID
   join Products p on od.ProductID = p.ProductID
   where o.Status = 'Completed'
   group by p.Category,
            DATE_TRUNC('week', o.ordertimestamp)::date
   having COUNT(distinct o.OrderID) >= 10),
     Numbered as
  (select Category,
          WeekStart,
          ROW_NUMBER() over (partition by Category
                             order by WeekStart) as rn
   from WeeklyCategoryActivity),
     Islands as
  (select Category,
          WeekStart,
          WeekStart - rn * INTERVAL '1 week' as grp
   from Numbered),
     Streaks as
  (select Category,
          MIN(WeekStart) as start_week,
          MAX(WeekStart) as end_week,
          COUNT(*) as streak_length
   from Islands
   group by Category,
            grp),
     Ranked as
  (select *,
          ROW_NUMBER() over (partition by Category
                             order by streak_length desc, end_week desc) as rnk
   from Streaks)
select Category,
       start_week,
       end_week,
       streak_length
from Ranked
where rnk = 1;