题解 | #各城市最大同时等车人数#
某宝店铺动销率与售罄率
http://www.nowcoder.com/practice/715dd44c994f45cb871afa98f1b77538
先从表格中计算出想得到的指标， 注意统计存货量inv和inv_value的时候，不要把两个表连接之后再分组统计，会导致重复计算
with t1 as
(select style_id, sum(inventory) inv,sum(tag_price*inventory) inv_value
from product_tb
group by style_id),
t2 as
(select style_id,sum(sales_price) GMV,sum(sales_num) sales
from
sales_tb
join
product_tb
using(item_id)
group by style_id
)
接着基于公式计算所需要的值（最终的sql)
with t1 as
(select style_id, sum(inventory) inv,sum(tag_price*inventory) inv_value
from product_tb
group by style_id),
t2 as
(select style_id,sum(sales_price) GMV,sum(sales_num) sales
from
sales_tb
join
product_tb
using(item_id)
group by style_id
)
select t1.style_id, round(sales/(inv-sales)*100,2) pin_rate,
round(GMV/inv_value*100,2) sell-through_rate
from
t1
join
t2
using(style_id)
order by 1
