题解 | #统计2021年10月每个退货率不大于0.5的商品各项指标#

第一步，按照商品计算2021年10月的点击数，展示数，加购数，付款数，退款数 第二步，计算商品点展比=点击数÷展示数； 加购率=加购数÷点击数； 成单率=付款数÷加购数；退货率=退款数÷付款数，并筛选退货率不大于0.5的

with t1 as(
select product_id, 
sum(if_click) click,
count(product_id) pro,
sum(if_cart) cart,
sum(if_payment) pay,
sum(if_refund) refund
from 
tb_user_event
where date_format(event_time,'%Y%m')='202110'
group by product_id) 

select product_id,
if(pro=0,0,round(click/pro,3)) ctr, 
if(click=0,0,round(cart/click,3)) cart_rate,
if(cart=0,0,round(pay/cart,3)) payment_rate,
if(pay=0,0,round(refund/pay,3)) refund_rate
from 
t1
where 
if(pay=0,0,round(refund/pay,3))<=0.5
order by product_id