题解 | #统计2021年10月每个退货率不大于0.5

# 求商品各项指标，具体包括点击率、加购率、成单率、退货率
# 要求在2021.10月退货率小于等于0.5

# 先计算时间范围内的各项指标，再筛选出退货率满足要求的

SELECT product_id, 
        ROUND(SUM(if_click)/COUNT(*), 3), # 点击率
        ROUND(SUM(if_cart)/SUM(if_click), 3), # 加购率
        ROUND(SUM(if_payment)/SUM(if_cart), 3), # 成单率
        ROUND(SUM(if_refund)/SUM(if_payment), 3) # 退货率
FROM tb_user_event
WHERE DATE(event_time) BETWEEN '2021-10-1' AND '2021-10-31'
GROUP BY product_id
# HAVING ROUND(SUM(if_refund)/SUM(if_payment), 3) <= 0.5
ORDER BY product_id

给的示例我都没筛选就通过了。

完整版：

# 求商品各项指标，具体包括点击率、加购率、成单率、退货率
# 要求在2021.10月退货率小于等于0.5

# 先计算时间范围内的各项指标，再筛选出退货率满足要求的
# 计算各个指标，处理分母可能为0的情况

SELECT product_id, 
        ROUND(SUM(if_click)/COUNT(*), 3), # 点击率
        ROUND(IF(SUM(if_click) = 0, 0, SUM(if_cart)/SUM(if_click)), 3), # 加购率
        ROUND(IF(SUM(if_cart) = 0, 0, SUM(if_payment)/SUM(if_cart)), 3), # 成单率
        ROUND(IF(SUM(if_payment) = 0, 0, SUM(if_refund)/SUM(if_payment)), 3) # 退货率
FROM tb_user_event
WHERE DATE(event_time) BETWEEN '2021-10-1' AND '2021-10-31'
GROUP BY product_id
HAVING ROUND(IF(SUM(if_payment) = 0, 0, SUM(if_refund)/SUM(if_payment)), 3) <= 0.5
ORDER BY product_id