题解 | 最畅销的SKU

WITH max_date AS (
    SELECT MAX(snapshot_date) AS max_dt FROM store_stock_
),
lsb as 
(SELECT ss.store_id, ss.sku_id,
sum(case when t.qty is null then 0 else t.qty end) as last7d_qty,
ROUND(sum(case when t.qty is null then 0 else t.qty end)/7, 2) as avg_daily_qty,
ss.stock_qty,
(CASE WHEN ROUND(sum(case when t.qty is null then 0 else t.qty end)/7, 2) > 0 THEN ROUND(ss.stock_qty/ROUND(sum(case when t.qty is null then 0 else t.qty end)/7, 2), 1) ELSE NULL END) as coverage_days,
row_number() over(partition by ss.store_id order by sum(case when t.qty is null then 0 else t.qty end) desc, ss.sku_id ASC) as rank_in_store
FROM store_stock_ ss  
LEFT JOIN (
select * from sales_daily_, max_date
where sale_date BETWEEN DATE_SUB(max_dt, INTERVAL 6 DAY) AND max_dt
) t
USING(store_id, sku_id)
group by ss.store_id, ss.sku_id, ss.stock_qty)
select *
from store_info_ si
join lsb l 
using(store_id)
where l.rank_in_store <= 3