题解 | 最畅销的SKU

select
    *
from
    (
        select
            ss.store_id,
            store_name,
            city,
            ss.sku_id,
            case
                when sum(qty) is null then 0
                else sum(qty)
            end as last7d_qty,
            ROUND(
                case
                    when sum(qty) is null then 0
                    else sum(qty)
                end / 7,
                2
            ) as avg_daily_qty,
            stock_qty,
            CASE
                WHEN ROUND(sum(qty) / 7, 2) > 0 THEN ROUND(stock_qty / ROUND(sum(qty) / 7, 2), 1)
                ELSE NULL
            END as coverage_days,
            row_number() over (
                partition by
                    ss.store_id
                order by
                    sum(qty) DESC,
                    sku_id ASC
            ) as rank_in_store
        from
            store_info_ as si
            right join store_stock_ as ss on ss.store_id = si.store_id
            left join sales_daily_ as sd on ss.sku_id = sd.sku_id
            and ss.store_id = sd.store_id
        where
            sd.sale_date >= DATE_SUB(snapshot_date, INTERVAL 7 DAY)
            or sd.sale_date is null
        group by
            ss.store_id,
            store_name,
            city,
            ss.sku_id,
            stock_qty
    ) t
where rank_in_store <= 3