Waves of Galactic Spending: Analyze Customer Spending Momentum Across Monthly Tiers

          with CustomerTotals as
  (select c.CustomerID,
          DATE_TRUNC('month', o.OrderDate)::date as OrderMonthStart,
          SUM(od.Quantity * od.UnitPrice) as OrderValue
   from Customers c
   join Orders o on c.CustomerID = o.CustomerID
   join OrderDetails od on o.OrderID = od.OrderID
   where o.Status = 'Completed'
   group by c.CustomerID,
            DATE_TRUNC('month', o.OrderDate)),
     RankedCustomers as
  (select CustomerID,
          SUM(OrderValue) as TotalValue
   from CustomerTotals
   group by CustomerID),
     Segmented as
  (select CustomerID,
          NTILE(4) over (
                         order by TotalValue desc) as SpendingTier
   from RankedCustomers),
     TierSpending as
  (select s.SpendingTier,
          ct.OrderMonthStart,
          SUM(ct.OrderValue) as TierOrderValue
   from CustomerTotals ct
   join Segmented s on ct.CustomerID = s.CustomerID
   group by s.SpendingTier,
            ct.OrderMonthStart),
     RollingTierAvg as
  (select SpendingTier,
          OrderMonthStart,
          TierOrderValue,
          AVG(TierOrderValue) over (partition by SpendingTier
                                    order by OrderMonthStart rows between 2 PRECEDING and current row) as Rolling3MonthAvg
   from TierSpending),
     WithPrev as
  (select SpendingTier,
          OrderMonthStart,
          TierOrderValue,
          Rolling3MonthAvg,
          LAG(Rolling3MonthAvg) over (partition by SpendingTier
                                      order by OrderMonthStart) as PrevRollingAvg
   from RollingTierAvg)
select SpendingTier,
       OrderMonthStart,
       TierOrderValue,
       Rolling3MonthAvg,
       PrevRollingAvg
from WithPrev
order by SpendingTier,
         OrderMonthStart;