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

SELECT
    category,
    orders_cnt,
    buyers_cnt,
    items_qty,
    revenue,
    avg_order_value,
    rank() over (
        order by
            revenue desc,
            orders_cnt desc,
            category
    ) as rank_by_revenue
FROM
    (
        SELECT
            category,
            count(distinct t1.order_id) as orders_cnt,
            count(distinct buyer_id) as buyers_cnt,
            sum(qty) as items_qty,
            sum(qty * price) as revenue,
            ROUND(sum(qty * price) / count(distinct t1.order_id), 2) as avg_order_value
        FROM
            order_items t1
            join orders t2 on t1.order_id = t2.order_id
            join product t3 on t1.product_id = t3.product_id
        where
            date_format(order_date, '%Y-%m') = '2024-08'
        group by
            category
    ) t
order by
    revenue desc,
    orders_cnt desc,
    category