题解 | #统计2021年10月的商品各项指标#

相关指标计算：

-- 商品点击率=点击数/展示数

-- 加购率=加购数/点击数

-- 成单率=付款数/加购数

-- 退货率=退款数/付款数

-- 商品点击率=点击数/展示数
-- 加购率=加购数/点击数
-- 成单率=付款数/加购数
-- 退货率=退款数/付款数
with t1 as(
select 
    product_id,
    count(if(if_click=1, 1, null)) as click_cnt,
    count(if_click) as display_cnt,
    count(if(if_cart=1, 1, null)) as cart_cnt,
    count(if(if_refund=1, 1, null)) as refund_cnt,
    count(if(if_payment=1, 1, null)) as pay_cnt
from tb_user_event
where  date_format(event_time, '%Y-%m') = '2021-10'
group by product_id
)
select 
    product_id,
    round(if(display_cnt > 0, click_cnt/ display_cnt, 0), 3) as ctr,
    round(if(click_cnt > 0, cart_cnt/click_cnt, 0),3) as cart_rate,
    round(if(cart_cnt > 0,pay_cnt/cart_cnt, 0), 3) as payment_rate,
    round(if(pay_cnt > 0, refund_cnt/pay_cnt, 0), 3) as refund_rate
from t1
group by product_id having refund_rate <= 0.5
order by product_id