题解 | 最畅销的SKU

with s1 as 
(
     SELECT s.store_id, s.sku_id, s.stock_qty,s.snapshot_date
FROM store_stock_ s
-- 派生表：先获取最新的 snapshot_date（仅1条）
INNER JOIN (
    SELECT snapshot_date 
    FROM store_stock_ 
    ORDER BY snapshot_date DESC 
    LIMIT 1
) AS latest_date 
ON s.snapshot_date = latest_date.snapshot_date
)

select store_id,store_name,city,sku_id,last7d_qty,
 avg_daily_qty,
stock_qty,
(case when last7d_qty >0 then round(stock_qty/avg_daily_qty,1) else null end
) as coverage_days,
rk as rank_in_store
from 
(select s1.store_id,t2.city,t2.store_name,s1.sku_id,
ifnull(sum(qty),0) as last7d_qty,
ifnull(round(sum(qty)/7,2),0.00) as avg_daily_qty,
row_number() over (partition by s1.store_id order by sum(qty) desc ) as rk,
(s1.stock_qty) as stock_qty
from s1
left join sales_daily_ t1
on (t1.sku_id=s1.sku_id and 
t1.sale_date between date_sub(s1.snapshot_date,interval 7 day) and date_add(s1.snapshot_date,interval 7 day)
and t1.store_id = s1.store_id)
left join store_info_ t2
on s1.store_id =t2.store_id
group by s1.store_id,s1.sku_id,t2.city,t2.store_name,s1.stock_qty)s3
where rk<=3