The Rare and the Profitable :Identify High-Revenue Products Among Low-Inventory Items Using Recent Sales Data Advanced

          with latest_order as
  (select max(orderdate) as latestorderdate
   from orders),
     recent_sales as
  (select p.productid,
          p.name as productname,
          sum(od.quantity * p.price) as totalrevenue
   from orderdetails od
   join orders o on od.orderid = o.orderid
   join products p on od.productid = p.productid
   cross join latest_order l
   where o.orderdate > l.latestorderdate - interval '30 days'
     and o.orderdate <= l.latestorderdate
   group by p.productid,
            p.name),
     inventory_totals as
  (select productid,
          sum(quantityavailable) as quantityavailable
   from inventory
   group by productid)
select r.productid,
       r.productname,
       i.quantityavailable,
       r.totalrevenue,
       ntile(4) over (
                      order by r.totalrevenue desc, r.productid) as demandtier
from recent_sales r
join inventory_totals i on r.productid = i.productid;