Supplier Order Gaps :Find Each Supplier's Longest Gap Between Order Dates Advanced

          with SupplierDays as
  (select distinct p.supplierid,
                   o.ordertimestamp::date as order_date
   from orders o
   join orderdetails od on o.orderid = od.orderid
   join products p on od.productid = p.productid),
     PreviousDays as
  (select supplierid,
          order_date as end_date,
          LAG(order_date) over (partition by supplierid
                                order by order_date) as start_date
   from SupplierDays),
     Gaps as
  (select supplierid,
          start_date,
          end_date,
          end_date - start_date as gap_days
   from PreviousDays
   where start_date is not null),
     Ranked as
  (select supplierid,
          start_date,
          end_date,
          gap_days,
          ROW_NUMBER() over (partition by supplierid
                             order by gap_days desc, end_date desc) as rn
   from Gaps)
select supplierid,
       start_date,
       end_date,
       gap_days
from Ranked
where rn = 1;