题解 | #统计2021年10月每个退货率不大于0.5的商品各项指标#
统计2021年10月每个退货率不大于0.5的商品各项指标
http://www.nowcoder.com/practice/cbf582d28b794722becfc680847327be
SELECT a.product_id,
if( show_cnt=0,0,round(sum(if_click)/show_cnt,3 )) as ctr,
if( sum(if_click)=0,0,round(sum(if_cart)/sum(if_click),3) ) as cart_rate,
if( sum(if_cart)=0,0,round(sum(if_payment)/sum(if_cart),3 ) ) as payment_rate,
if( sum(if_payment)=0,0,round(sum(if_refund)/sum(if_payment),3) ) as refund_rate
from tb_user_event tb
JOIN
(select product_id,count(uid) show_cnt from tb_user_event
where date_format(event_time,"%Y-%m")='2021-10'
GROUP BY product_id
)a
on tb.product_id=a.product_id
where date_format(event_time,"%Y-%m")='2021-10'
GROUP BY a.product_id
having refund_rate<=0.5
ORDER BY product_id asc;