题解 | #统计2021年10月退货率不大于0.5#

select e2.product_id,
    round(e2.click/e2.num,3) as ctr,
    round( e2.cart/e2.click,3) as cart_rate,
    round( e2.payment/e2.cart,3) as payment_rate,
    round( e2.refund/e2.payment,3) as refund_rate
from (
    select e1.product_id,
    count(*) as num,
    sum(if_click) as click,
    sum(if_cart) as cart,
    sum(if_payment) as payment,
    sum(if_refund) as refund
    from (select * 
        from tb_user_event
        where year(event_time)=2021 and month(event_time) =10) e1

    group by e1.product_id
    )e2 
where e2.payment=0 or e2.refund/e2.payment<=0.5
order by e2.product_id