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

  select 
	product_id,
    round(if(count(*)=0,0,sum(if_click)/count(*)),3) ctr, -- 商品点展比
    round(if(sum(if_click)=0,0,sum(if_cart)/sum(if_click)),3) cart_rate, -- 加购率=加购数÷点击数
    round(if(sum(if_cart)=0,0,sum(if_payment)/sum(if_cart)),3) payment_rate, -- 成单率=付款数÷加购数；
    round(if(sum(if_payment)=0,0,sum(if_refund)/sum(if_payment)),3) refund_rate -- 退货率=退款数÷付款数， 
  from 
	tb_user_event
  where date_format(event_time,'%Y%m')='202110' -- 2021年10月每个有展示记录
  group by product_id
  having refund_rate <= 0.5 -- 退货率不大于0.5
  order by product_id --按商品ID升序