题解 | 目标月份的品类销售简报

with  t1 as (
    select category,
           count(distinct order_id) as orders_cnt,
           count(distinct buyer_id) as buyers_cnt,
           sum(qty) as items_qty,
           sum(qty*price) as revenue
           from product join order_items using (product_id)
           join orders using (order_id)
           where order_date between '2024-08-01' and '2024-08-31'
           group by category
)
select category,
       orders_cnt,
       buyers_cnt,
       items_qty,
       revenue,
       round(revenue/orders_cnt,2) as avg_order_value,
       rank() over ( order by revenue desc ,orders_cnt desc  ,category) as rank_by_revenue

from t1
order by revenue desc ,orders_cnt desc ,category