题解 | 最畅销的SKU

with a1 as (
    select c.store_id,c.sku_id,sum(qty) as last7d_qty
    from sales_daily_ c 
    join store_stock_ b 
    on b.store_id = c.store_id and b.sku_id = c.sku_id
    where datediff(snapshot_date,sale_date) between 0 and 6
    group by c.store_id,c.sku_id #0无记录
),

a2 as (
	select 
	b.store_id,
	store_name,
	city,
	b.sku_id,
	round(coalesce(last7d_qty,0),0) as last7d_qty,
	round(coalesce(last7d_qty,0.00)/7,2) as avg_daily_qty,
	stock_qty,
	ROUND(stock_qty/(round(coalesce(last7d_qty,0.00)/7,2)),1) as coverage_days,
	ROW_NUMBER() over(partition by b.store_id order by last7d_qty DESC, sku_id ASC) as rank_in_store
	from store_stock_ b 
	left join a1 
	on b.store_id = a1.store_id and b.sku_id = a1.sku_id
	join store_info_ a on b.store_id=a.store_id
	order by b.store_id,rank_in_store)

select 
store_id,
store_name,
city,
sku_id,
last7d_qty,
avg_daily_qty,
stock_qty,
coverage_days,
rank_in_store
from a2
where rank_in_store<4