Constellations of Consistency :Identify Suppliers with the Longest Consecutive Fulfillment Streaks and Analyze Reliability Patterns Advanced

          with SupplierDays as
  (select distinct p.SupplierID,
                   o.OrderDate::date as OrderDate
   from Orders o
   join OrderDetails od on o.OrderID = od.OrderID
   join Products p on od.ProductID = p.ProductID),
     Numbered as
  (select SupplierID,
          OrderDate,
          ROW_NUMBER() over (partition by SupplierID
                             order by OrderDate) as rn
   from SupplierDays),
     Islands as
  (select SupplierID,
          OrderDate,
          (OrderDate - rn * INTERVAL '1 day') as grp
   from Numbered),
     Streaks as
  (select SupplierID,
          MIN(OrderDate) as start_date,
          MAX(OrderDate) as end_date,
          COUNT(*) as streak_length
   from Islands
   group by SupplierID,
            grp),
     Ranked as
  (select *,
          ROW_NUMBER() over (partition by SupplierID
                             order by streak_length desc, end_date desc) as rnk
   from Streaks)
select SupplierID,
       start_date,
       end_date,
       streak_length
from Ranked
where rnk = 1
order by streak_length desc,
         end_date desc;