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

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