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

select
    product_id,
    round(click_cnt / show_cnt, 3) as ctr,
    round(IF (click_cnt > 0, cart_cnt / click_cnt, 0), 3) as cart_rate,
    round(IF (cart_cnt > 0, payment_cnt / cart_cnt, 0), 3) as payment_rate,
    round(IF (payment_cnt > 0, refund_cnt / payment_cnt, 0),3) as refund_rate
from
    (
        select
            product_id,
            COUNT(1) as show_cnt,
            sum(if_click) as click_cnt,
            sum(if_cart) as cart_cnt,
            sum(if_payment) as payment_cnt,
            sum(if_refund) as refund_cnt
        from
            tb_user_event
        where
            DATE_FORMAT (event_time, '%Y%m') = '202110'
        group by
            product_id
    ) as t_product_index_cnt
where payment_cnt = 0 or refund_cnt / payment_cnt <= 0.5
order by
    product_id;

建立新表存储总展示数，点击数，加入购物车数，支付书和退货数，确定计算范围为2021-10，展示数为1，按照商品编号分组

使用新表中数据算出各个指标，注意计算三位小数和取0值的情况，确定范围为退货率<=0.5和没有支付的商品，按照商品编号顺序输出