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

WITH t1 AS (
    SELECT
        product_id,
        count(product_id) AS show_num,
        sum(if_click) AS click_num,
        sum(if_cart) AS cart_num,
        sum(if_payment) AS pay_num,
        sum(if_refund) AS refund_num
    FROM tb_user_event
    WHERE SUBSTR(event_time,1,7) = '2021-10'
    GROUP BY product_id
)
SELECT product_id,
       ROUND(click_num / show_num,3) AS ctr,
       ROUND(cart_num / click_num,3) AS cart_rate,
       ROUND(pay_num / cart_num,3) AS payment_rate,
       ROUND(refund_num / pay_num,3) AS refund_rate
       FROM t1
HAVING refund_rate <= 0.5
ORDER BY product_id ASC
;

# 这道题关键在于不大于0.5