题解 | 最畅销的SKU

with a as(
    select 
        ss.store_id,
        store_name,
        city,
        ss.sku_id,
        ifnull(sum(qty),0) as last7d_qty,
        ifnull(round(sum(qty)/7,2),0.00) as avg_daily_qty,
        ss.stock_qty,
        CASE WHEN round(sum(qty)/7,2)>0 THEN ROUND(ss.stock_qty/round(sum(qty)/7,2), 1) ELSE NULL END as coverage_days,
        ROW_NUMBER() over(partition by store_id order by sum(qty) DESC,sku_id ) as rank_in_store
    from store_stock_ ss 
        left join store_info_ si using(store_id)
        left join (
            select  sales_daily_.*
            from sales_daily_  join store_stock_ using(store_id,sku_id )
            where sale_date between subdate(snapshot_date,6) and snapshot_date 
            ) sd using(store_id,sku_id )
    
    group by ss.store_id,ss.sku_id,store_name,city,ss.stock_qty

)
select 
    store_id,
    store_name,
    city,
    sku_id,
    last7d_qty,
    avg_daily_qty,
    stock_qty,
    coverage_days,
    rank_in_store
from a
where rank_in_store<=3;