Order Cadence Explorer :Rank Customers by Order Frequency and Analyze Average Time Gaps Between Purchases Advanced

          with customer_lags as
  (select o.customerid,
          o.orderid,
          o.orderdate,
          o.orderdate - lag(o.orderdate) over (partition by o.customerid
                                               order by o.orderdate) as gap
   from orders o),
     aggregated as
  (select customerid,
          count(orderid) as totalorders,
          avg(gap) as avgtimebetweenorders
   from customer_lags
   group by customerid)
select customerid,
       totalorders,
       rank() over (
                    order by totalorders desc) as orderrank,
                   avgtimebetweenorders::text
from aggregated
order by totalorders desc,
         avgtimebetweenorders::text;