题解 | 最畅销的SKU

with t as (
select store_id,store_name,city,sku_id,
ifnull(sum(qty),0) as last7d_qty, 
round(ifnull(sum(qty),0)/7,2) as avg_daily_qty,
max(stock_qty) as stock_qty,
CASE 
WHEN round(ifnull(sum(qty),0)/7,2)>0 
THEN ROUND(max(stock_qty)/round(ifnull(sum(qty),0)/7,2), 1) 
ELSE NULL 
END as coverage_days,
ROW_NUMBER()OVER(partition by store_id order by ifnull(sum(qty),0) desc,sku_id ASC) as rank_in_store
from store_info_
join store_stock_ using(store_id)
left join sales_daily_ using(store_id,sku_id)
group by store_id,store_name,city,sku_id
)

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