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

# 子查询
SELECT 
    product_id,
    ROUND(IF(show_num > 0, click_num/ show_num, 0), 3)    as ctr,
    ROUND(IF(click_num > 0, cart_num / click_num, 0 ), 3)    as cart_rate,
    ROUND(IF(cart_num > 0, payment_num / cart_num, 0), 3)    as payment_rate,
    ROUND(IF(payment_num > 0, refund_num / payment_num, 0), 3)    as refund_rate
FROM
(
    SELECT
        product_id,
        COUNT(product_id) as show_num,
        SUM(if_click) as click_num,
        SUM(if_cart) as cart_num,
        SUM(if_payment) as payment_num,
        SUM(if_refund) as refund_num
    FROM 
        tb_user_event as t
    WHERE 
        DATE_FORMAT(event_time,'%Y-%m') = '2021-10'
    GROUP BY 
        product_id  

) as tt

HAVING 
    refund_rate <= 0.5
ORDER BY
    product_id ASC;