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

SELECT
product_id,
IF(COUNT(if_click = 1 OR NULL) = 0, 0.000, ROUND(COUNT(if_click = 1 OR NULL) /COUNT(*), 3)) AS ctr,
IF(COUNT(if_cart = 1 OR NULL) = 0, 0.000, ROUND(COUNT(if_cart = 1 OR NULL) /COUNT(if_click = 1 OR NULL), 3)) AS cart_rate,
IF(COUNT(if_payment = 1 OR NULL) = 0, 0.000, ROUND(COUNT(if_payment = 1 OR NULL) /COUNT(if_cart = 1 OR NULL), 3)) AS payment_rate,
IF(COUNT(if_refund = 1 OR NULL) = 0, 0.000, ROUND(COUNT(if_refund = 1 OR NULL) /COUNT(if_payment = 1 OR NULL), 3)) AS refund_rate

FROM tb_user_event

WHERE DATE_FORMAT(event_time, '%Y-%m') = '2021-10'

GROUP BY product_id

HAVING refund_rate <= '0.5'

ORDER BY product_id