题解 | #每个6/7级用户活跃情况#
统计2021年10月每个退货率不大于0.5的商品各项指标
http://www.nowcoder.com/practice/cbf582d28b794722becfc680847327be
select product_id,
if(show_cnt = 0, 0, round(click_cnt/ show_cnt,3)) as ctr,
if(click_cnt = 0,0, round(cart_cnt/ click_cnt,3)) as cart_rate,
if(cart_cnt = 0, 0, round(pay_cnt/ cart_cnt,3)) as payment_rate,
if(pay_cnt = 0, 0, round(refund_cnt/ pay_cnt,3)) as refund_rate
from(
select product_id, count(product_id) as show_cnt
, sum(if_click) as click_cnt
, sum(if_cart) as cart_cnt
, sum(if_payment) as pay_cnt
, sum(if_refund) as refund_cnt
from tb_user_event
where date_format(event_time,'%Y-%m') = '2021-10'
group by product_id
) as t1
group by product_id
having refund_rate <= 0.5
order by product_id
先创建子表，把要用的展示数、点击数、加购数、付款数、退款数全部调出来
然后再计算所求的率
查看30道真题和解析