题解 | #统计退货率不大于0.5的商品各项指标#

select t.product_id, round(sum(if_click)/count(if_click),3) as ctr,
case when sum(if_click)=0 then 0
else round(sum(if_cart)/sum(if_click),3) end as cart_rate,
case when sum(if_cart)=0 then 0
else round(sum(if_payment)/sum(if_cart),3) end as payment_rate,
case when sum(if_payment)=0 then 0
else round(sum(if_refund)/sum(if_payment),3) end as refund_rate
from tb_user_event, (
# 1. 2021年10月每个有展示记录的退货率不大于0.5的商品
select product_id, round(sum(if_refund)/count(if_refund),3) as refund_rate 
from tb_user_event
where year(event_time)=2021 and month(event_time)=10
group by product_id
having  refund_rate<=0.5
)t
where tb_user_event.product_id = t.product_id and year(event_time)=2021 and month(event_time)=10
group by t.product_id
order by t.product_id