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

/*
先算出退货率小于0.5的商品
然后以同样的计算方法计算出其他几个指标
*/

WITH        -- 使用CTE更加直观
t1 AS (     -- 先计算出退货率不大于0.5的商品，用作自连接过滤
    SELECT
        product_id
    FROM tb_user_event
    WHERE event_time >= '2021-10-01 00:00:00'
        AND event_time < '2021-11-01 00:00:00'
    GROUP BY product_id
    HAVING COALESCE(SUM(if_refund) / NULLIF(SUM(if_payment),0),0) <= 0.5
)

SELECT      -- 用自连接过滤完退货率不大于0.5的商品后，计算各项指标
    a.product_id,
    ROUND(COALESCE(SUM(a.if_click) / COUNT(*),0),3) AS ctr,
    ROUND(COALESCE(SUM(a.if_cart) / NULLIF(SUM(if_click),0),0),3) AS cart_rate,
    ROUND(COALESCE(SUM(a.if_payment) / NULLIF(SUM(a.if_cart),0),0),3) AS payment_rate,
    ROUND(COALESCE(SUM(a.if_refund) / NULLIF(SUM(a.if_payment),0),0),3) AS refund_rate
FROM tb_user_event AS a
INNER JOIN t1
    ON (t1.product_id = a.product_id)
    WHERE event_time >= '2021-10-01 00:00:00'
        AND event_time < '2021-11-01 00:00:00'
GROUP BY a.product_id
ORDER BY a.product_id;