Galactic High Spenders by Week :Track Weekly Top-Spending Customers with Aggregate Window Functions Advanced

          with line as
  (select o.customerid,
          DATE_TRUNC('week', o.orderdate)::date as week_start,
          od.quantity * od.unitprice as line_amount
   from orders o
   join orderdetails od on od.orderid = o.orderid), per_customer_week as
  (select customerid,
          week_start,
          SUM(line_amount) over (partition by customerid,
                                              week_start) as total_spend
   from line),
                                                    dedup as
  (select distinct customerid,
                   week_start,
                   total_spend
   from per_customer_week),
                                                    scored as
  (select customerid,
          week_start,
          total_spend,
          MAX(total_spend) over (partition by week_start) as max_total_in_week,
                                RANK() over (partition by week_start
                                             order by total_spend desc) as top_rank
   from dedup)
select customerid,
       week_start,
       total_spend,
       (total_spend = max_total_in_week) as is_top_spender,
       top_rank
from scored
order by week_start,
         total_spend desc,
         customerid;