题解 | #各城市最大同时等车人数#

先从表格中计算出想得到的指标， 注意统计存货量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