题解 | #每个6/7级用户活跃情况#

select product_id, 
    if(show_cnt = 0, 0, round(click_cnt/ show_cnt,3)) as ctr,
    if(click_cnt = 0,0, round(cart_cnt/ click_cnt,3)) as cart_rate, 
    if(cart_cnt = 0, 0, round(pay_cnt/ cart_cnt,3)) as payment_rate,
    if(pay_cnt = 0, 0, round(refund_cnt/ pay_cnt,3)) as refund_rate
from(
    select product_id, count(product_id) as show_cnt
        , sum(if_click) as click_cnt
        , sum(if_cart) as cart_cnt
        , sum(if_payment) as pay_cnt
        , sum(if_refund) as refund_cnt
    from tb_user_event
    where date_format(event_time,'%Y-%m') = '2021-10'
    group by product_id
    ) as t1
group by product_id
having refund_rate <= 0.5
order by product_id

先创建子表，把要用的展示数、点击数、加购数、付款数、退款数全部调出来

然后再计算所求的率