The Waning Moon of Commerce :Detect and Rank the Longest Consecutive Periods of Low Revenue to Analyze Business Slowdowns Advanced

          with DailyRevenue as
  (select OrderDate::date as OrderDate,
          SUM(TotalAmount) as DailyRevenue
   from Orders
   group by OrderDate::date
   having SUM(TotalAmount) < 25000),
     Numbered as
  (select OrderDate,
          ROW_NUMBER() over (
                             order by OrderDate) as rn
   from DailyRevenue),
     Islands as
  (select OrderDate,
          OrderDate - rn * INTERVAL '1 day' as grp
   from Numbered),
     GroupedIslands as
  (select MIN(OrderDate) as start_date,
          MAX(OrderDate) as end_date,
          COUNT(*) as slowdown_length
   from Islands
   group by grp),
     RankedIslands as
  (select start_date,
          end_date,
          slowdown_length,
          DENSE_RANK() over (
                             order by slowdown_length desc) as len_rank
   from GroupedIslands)
select start_date,
       end_date,
       slowdown_length
from RankedIslands
where len_rank <= 5
order by len_rank,
         start_date;