with a as (select store_id,sku_id,stock_qty,snapshot_date,max(snapshot_date) over(partition by store_id,sku_id ) max_sdate
from store_stock_ 
),
b as(
select a.store_id,a.sku_id,ifnull(sum(qty),0) last7d_qty ,max(stock_qty) stock_qty,ifnull(round(sum(qty)/7,2),0) avg_daily_qty,row_number() over(partition by a.store_id order by ifnull(sum(qty),0) desc,a.sku_id asc) rank_in_store
from  a
left join sales_daily_ sd 
on a.store_id = sd.store_id
and a.sku_id = sd.sku_id
and datediff(max_sdate,sale_date)<=7
where snapshot_date=max_sdate
group by a.store_id,a.sku_id)

select b.store_id,store_name,city,b.sku_id,last7d_qty,avg_daily_qty,stock_qty,CASE WHEN avg_daily_qty>0 THEN ROUND(stock_qty/avg_daily_qty, 1) ELSE NULL END as coverage_days,rank_in_store
from b 
inner join store_info_ si
on b.store_id=si.store_id
where rank_in_store<=3