Unbroken Chains of Loyalty :Find Customers with the Longest Consecutive Monthly Order Streaks and Analyze Engagement Durations Advanced

          with MonthlyOrders as
  (select CustomerID,
          CAST(DATE_TRUNC('month', OrderDate) as DATE) as OrderMonth
   from Orders
   group by CustomerID,
            DATE_TRUNC('month', OrderDate)),
     Numbered as
  (select CustomerID,
          OrderMonth,
          ROW_NUMBER() over (partition by CustomerID
                             order by OrderMonth) as rn
   from MonthlyOrders),
     Grouped as
  (select CustomerID,
          OrderMonth,
          rn,
          OrderMonth - (rn * INTERVAL '1 month') as grp
   from Numbered),
     Islands as
  (select CustomerID,
          MIN(OrderMonth) as StreakStart,
          MAX(OrderMonth) as StreakEnd,
          COUNT(*) as StreakLength
   from Grouped
   group by CustomerID,
            grp),
     Ranked as
  (select CustomerID,
          StreakStart,
          StreakEnd,
          StreakLength,
          ROW_NUMBER() over (partition by CustomerID
                             order by StreakLength desc) as rn
   from Islands)
select CustomerID,
       StreakStart,
       StreakEnd,
       StreakLength
from Ranked
where rn = 1
order by StreakLength desc;