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

SELECT product_id, 
ROUND(SUM(IF(if_click = 1, 1, 0)) / COUNT(*), 3) AS ctr, 

ROUND(IF(SUM(IF(if_click = 1, 1, 0)) != 0,SUM(IF(if_cart = 1, 1, 0)) / SUM(IF(if_click = 1, 1, 0)), 0), 3) AS cart_rate, 

ROUND(IF(SUM(IF(if_cart = 1, 1, 0)) != 0, SUM(IF(if_payment = 1, 1, 0)) / SUM(IF(if_cart = 1, 1, 0)), 0), 3) AS payment_rate,

ROUND(IF(SUM(IF(if_payment = 1, 1, 0)), SUM(IF(if_refund = 1, 1, 0)) / SUM(IF(if_payment = 1, 1, 0)), 0), 3) AS refund_rate

FROM tb_user_event
WHERE YEAR(event_time) = 2021 AND MONTH(event_time) = 10
GROUP BY product_id
HAVING ROUND(SUM(IF(if_refund = 1, 1, 0)) /SUM(IF(if_payment = 1, 1, 0)), 3) <= 0.5
ORDER BY product_id;