题解 | 最畅销的SKU

with tmp as(
    select
        i.store_id,
        i.store_name,
        i.city,
        t.sku_id,
        coalesce(round(sum(s.qty),2),0) as last7d_qty,
        coalesce(round(sum(s.qty)/7,2),0) as avg_daily_qty,
        row_number() over(partition by i.store_id order by coalesce(round(sum(s.qty),2),0) desc,t.sku_id) as rank_in_store
    from store_info_ i
    left join store_stock_ t on i.store_id=t.store_id
    left join sales_daily_ s on i.store_id=s.store_id and t.sku_id=s.sku_id and datediff(t.snapshot_date,s.sale_date) between 0 and 6
    group by i.store_id,i.store_name,i.city,t.sku_id)

select
    m.store_id,
    m.store_name,
    m.city,
    m.sku_id,
    m.last7d_qty,
    m.avg_daily_qty,
    t.stock_qty,
    case
        when m.avg_daily_qty>0 then round(t.stock_qty/m.avg_daily_qty,1) 
        else null end as coverage_days,
    m.rank_in_store
from store_stock_ t right join tmp m on t.store_id=m.store_id and t.sku_id=m.sku_id
where m.rank_in_store<=3