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

大厂笔试SQL刷完了 祝我找份合适的工作

with t1 as(
    select category,count(distinct oi.order_id) orders_cnt,
    count(distinct buyer_id) buyers_cnt,
    sum(qty) items_qty,
    sum(qty*price) revenue,
    round(sum(qty*price)/count(distinct oi.order_id),2) avg_order_value
    from product p left join order_items oi on p.product_id = oi.product_id
    join orders o on oi.order_id = o.order_id
    where mid(order_date,1,7) = '2024-08'
    group by category
)
select category,orders_cnt,buyers_cnt,items_qty,revenue,avg_order_value,
rank()over(order by revenue desc,orders_cnt desc,category)  rank_by_revenue
from t1
order by revenue desc,orders_cnt desc,category