--连表需要注意，不能直接连，看好关系
--这段求last7d_qty，avg_daily_qty，stock_qty
with tiaojian as (
select 
sf.store_id,
sf.store_name,
sf.city,
st.sku_id,
ifnull(sum(qty),0) as last7d_qty ,
round(
ifnull(sum(qty)/7,0),2) as avg_daily_qty,
round(
ifnull(
avg(stock_qty),0),0) as stock_qty
from store_info_ sf left join store_stock_ st on 
sf.store_id=st.store_id
left join sales_daily_ sy on sy.store_id=st.store_id 
and sy.sku_id=st.sku_id
and 
datediff(snapshot_date,sale_date)<=6
group by sf.store_id,sf.city,sf.store_name,st.sku_id
--求 coverage_days
),tiaojian1 as (
select 
store_id,
store_name,
city,
sku_id,
case when avg_daily_qty>0 then ROUND(stock_qty/avg_daily_qty,1) end as coverage_days
from tiaojian 
),
--求rank_in_store
tiaojian2 as (
select 
store_id,
store_name,
city,
sku_id,
py
from(
select 
store_id,
store_name,
city,
sku_id,
last7d_qty ,
row_number()over(partition by store_name,city order by last7d_qty  desc) as py
from tiaojian 
) as t 
where 
t.py<=3
)

--整合
select 
t.store_id,
t.store_name,
t.city,
t.sku_id,
last7d_qty,
avg_daily_qty,
stock_qty,
coverage_days,
py as rank_in_store
from tiaojian t inner join tiaojian1 t1 on t.store_id=t1.store_id
and t.store_name=t1.store_name and t.city=t1.city and t.sku_id=t1.sku_id
inner join tiaojian2 t2 on t.store_id=t2.store_id
and t.store_name=t2.store_name and t.city=t2.city and t.sku_id=t2.sku_id