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

SELECT product_id, 
    ROUND(COUNT(IF(if_click = 1, product_id, NULL)) / COUNT(event_time), 3) AS ctr,
    ROUND(COUNT(IF(if_cart = 1, product_id, NULL)) / COUNT(IF(if_click = 1, product_id, NULL)), 3) AS cart_rate,
    ROUND(COUNT(IF(if_payment = 1, product_id, NULL)) / COUNT(IF(if_cart = 1, product_id, NULL)), 3) AS payment_rate,
    ROUND(COUNT(IF(if_refund = 1, product_id, NULL)) / COUNT(IF(if_payment = 1, product_id, NULL)), 3) AS refund_rate
FROM tb_user_event
WHERE event_time IS NOT NULL 
    AND SUBSTRING(event_time, 1, 7) = '2021-10'
GROUP BY product_id
HAVING COUNT(IF(if_refund = 1, product_id, NULL)) / COUNT(IF(if_payment = 1, product_id, NULL)) <= 0.5
ORDER BY product_id