题解 | 最畅销的SKU

# store_info_、store_stock_、sales_daily_
# store_id 、 sku_id
# 连锁门店在每晚库存快照日需要快速评估近 7 天销量与库存覆盖天数（库存 ÷ 近 7 天日均销量），并挑出每家门店近 7 天最畅销的 Top3 SKU 以指导补货
# 门店
# 以最新库存快照日期为统计基准日，计算每家门店在该快照日的 Top3 商品（近 7 天销量排行，统计窗口为"基准日向前 7 天，含基准日"）
# 1、找出最新库存快照时间和基准日
with s_date as (
select max(snapshot_date) as last_date,
       date_sub(max(snapshot_date),interval 7 day) as start_date
from store_stock_),
# 2、在日期范围内计算指标
date1 as (
select s.store_id,s.sku_id,
       ifnull(sum(s.qty),0) as last7d_qty,
       round(ifnull(sum(s.qty),0) / 7 ,2) as avg_daily_qty
from sales_daily_ s
join s_date d 
where s.sale_date >= d.start_date and s.sale_date <= d.last_date
group by store_id,s.sku_id),
# 3、计算快照日的库存
max_date as(
select s.store_id,s.sku_id,
       sum(stock_qty) as stock_qty
from store_stock_ s
join s_date d 
where s.snapshot_date = d.last_date
group by store_id,s.sku_id)

select *
from (
select i.store_id,i.store_name,i.city,s.sku_id,
       ifnull(d.last7d_qty,0) as last7d_qty,
       ifnull(d.avg_daily_qty,0) as avg_daily_qty,
       ifnull(m.stock_qty,0) as stock_qty,
       (CASE WHEN ifnull(d.avg_daily_qty,0) > 0 THEN ROUND(ifnull(m.stock_qty,0)/ifnull(d.avg_daily_qty,0), 1) ELSE NULL END) as coverage_days,
       row_number() over(partition by i.store_id order by  ifnull(d.last7d_qty,0) desc,s.sku_id asc) as rank_in_store
from store_info_ i
join store_stock_ s on s.store_id = i.store_id
left join date1 d on i.store_id = d.store_id and s.sku_id =d.sku_id
left join max_date m on i.store_id = m.store_id and s.sku_id =m.sku_id) t 
where rank_in_store <= 3