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

select *
from
    (select product_id
    ,(case when count(id) = 0 then 0 else round(sum(case when if_click = 1 then 1 else 0 end)/count(id),3) end )as ctr
    ,(case when sum(case when if_click = 1 then 1 else 0 end) = 0 then 0 else round(sum(case when if_cart = 1 then 1 else 0 end)/sum(case when if_click = 1 then 1 else 0 end),3) end ) as cart_rate
    ,(case when sum(case when if_cart = 1 then 1 else 0 end) = 0 then 0 else round(sum(case when if_payment = 1 then 1 else 0 end)/sum(case when if_cart = 1 then 1 else 0 end),3) end ) as payment_rate
    ,(case when sum(case when if_cart = 1 then 1 else 0 end) = 0 then 0 else round(sum(case when if_refund = 1 then 1 else 0 end)/sum(case when if_payment = 1 then 1 else 0 end),3) end ) as refund_rate
    from tb_user_event
    where date_format(event_time,'%Y-%m') = '2021-10'
    group by product_id) as T
where T.refund_rate <= 0.5
order by T.product_id