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

select t.product_id,
round(if(count(1)!=0,sum(if_click)/count(1),0),3),
round(if(sum(if_click)!=0,sum(if_cart)/sum(if_click),0),3),
round(if(sum(if_cart)!=0,sum(if_payment)/sum(if_cart),0),3),
round(if(sum(if_payment)!=0,sum(if_refund)/sum(if_payment),0),3)

from tb_user_event e
join
(
select
distinct product_id,
case when sum(if_payment) = 0 then 0 Else
sum(if_refund)/sum(if_payment) end as th
from tb_user_event
where date_format(event_time,"%Y-%m") = "2021-10"
group by 1
having th<= 0.5
)t
on e.product_id = t.product_id
where date_format(e.event_time,"%Y-%m") = "2021-10"
group by 1

order by product_id asc