首页 > 试题广场 >

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

[编程题]统计2021年10月每个退货率不大于0.5的商品各项指标
  • 热度指数：62907 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

现有用户对展示的商品行为表tb_user_event

id uid product_id event_time if_click if_cart if_payment if_refund
1 101 8001 2021-10-01 10:00:00 0 0 0 0
2 102 8001
 2021-10-01 10:00:00
 1 0 0 0
3 103 8001
 2021-10-01 10:00:00
 1 1 0 0
4 104 8001
 2021-10-02 10:00:00
 1 1 1 0
5 105 8001
 2021-10-02 10:00:00
 1 1 1 0
6 101 8002
 2021-10-03 10:00:00
 1 1 1 0
7 109 8001
 2021-10-04 10:00:00
 1 1 1 1
（uid-用户ID, product_id-商品ID, event_time-行为时间, if_click-是否点击, if_cart-是否加购物车, if_payment-是否付款, if_refund-是否退货退款）

问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，

注：
  • 商品点展比=点击数÷展示数；
  • 加购率=加购数÷点击数；
  • 成单率=付款数÷加购数；退货率=退款数÷付款数，
  • 当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

输出示例：
示例数据的输出结果如下

product_id ctr cart_rate payment_rate refund_rate
8001 0.833 0.800 0.750 0.333
8002 1.000 1.000 1.000 0.000

解释：
在2021年10月商品8001被展示了6次，点击了5次，加购了4次，付款了3次，退款了1次，因此点击率为5/6=0.833，加购率为4/5=0.800，
成单率为3/4=0.750，退货率为1/3=0.333（保留3位小数）；
示例1

输入

DROP TABLE IF EXISTS tb_user_event;
CREATE TABLE tb_user_event (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    product_id INT NOT NULL COMMENT '商品ID',
    event_time datetime COMMENT '行为时间',
    if_click TINYINT COMMENT '是否点击',
    if_cart TINYINT COMMENT '是否加购物车',
    if_payment TINYINT COMMENT '是否付款',
    if_refund TINYINT COMMENT '是否退货退款'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_event(uid, product_id, event_time, if_click, if_cart, if_payment, if_refund) VALUES
  (101, 8001, '2021-10-01 10:00:00', 0, 0, 0, 0),
  (102, 8001, '2021-10-01 10:00:00', 1, 0, 0, 0),
  (103, 8001, '2021-10-01 10:00:00', 1, 1, 0, 0),
  (104, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
  (105, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
  (101, 8002, '2021-10-03 10:00:00', 1, 1, 1, 0),
  (109, 8001, '2021-10-04 10:00:00', 1, 1, 1, 1);

输出

8001|0.833|0.800|0.750|0.333
8002|1.000|1.000|1.000|0.000
算法知识视频讲解
添加回答
Mysql
王延德头像 王延德
select product_id,
round(click_cnt/play_cnt,3) as ctr,
round(cart_cnt/click_cnt,3) as cart_rate,
round(payment_cnt/cart_cnt,3) as payment_rate,
round(refund_cnt/payment_cnt,3) as refund_rate
from (select product_id,
count(product_id) as play_cnt,
count(if(if_click=1,1,null)) as click_cnt,
count(if(if_cart=1,1,null)) as cart_cnt,
count(if(if_payment=1,1,null)) as payment_cnt,
count(if(if_refund=1,1,null)) as refund_cnt
from tb_user_event where date_format(event_time,'%Y-%m')='2021-10' group by product_id) t1
where round(refund_cnt/payment_cnt,3) <= 0.5
order by product_id asc;
发表于 2025-01-22 10:31:07 回复(0)
Mysql
牛客557177841号头像 牛客557177841号 
select 
product_id,
round(ifnull(sum(if_click)/nullif(count(id),0),0),3) ctr,
round(ifnull(sum(if_cart)/if(sum(if_click)=0,null,sum(if_click)),0),3) cart_rate,
round(ifnull(sum(if_payment)/if(sum(if_cart)=0,null,sum(if_cart)),0) ,3)payment_rate,
round(ifnull(sum(if_refund)/if(sum(if_payment)=0,null,sum(if_payment)),0),3)  refund_rate
from tb_user_event tue
where date_format(event_time,'%Y-%m')= '2021-10'
group by product_id 
having refund_rate <= 0.5
order by 1

发表于 2024-10-13 11:48:39 回复(0)
Mysql
牛客393777248号头像 牛客393777248号 
with a as (
SELECT
product_id,
count(*) 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') = '2021-10' 
GROUP BY product_id
having round(sum(if_refund) / sum(if_payment),3) <= 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 a 
order by product_id

发表于 2024-09-18 13:39:33 回复(0)
Mysql
小金桔_头像 小金桔_
select
    product_id,
    round(sum(if_click)/count(*),3) ctr,
    if(sum(if_click)=0,0,round(sum(if_cart)/sum(if_click),3)) cart_rate,
    if(sum(if_cart)=0,0,round(sum(if_payment)/sum(if_cart),3)) payment_rate,
    if(sum(if_payment)=0,0,round(sum(if_refund)/sum(if_payment),3)) refund_rate
from tb_user_event
where date_format(event_time,"%Y-%m")='2021-10'
group by product_id
order by product_id
发表于 2024-07-01 16:31:32 回复(0)
Mysql
祖拜尔实习生头像 祖拜尔实习生 
select
    product_id,
    round(avg(if_click),3) click_rate,
    round(
        count(case when if_cart = 1 then 1 else null end)
        /count(case when if_click = 1 then 1 else null end)
        ,3) cart_rate,
    round(
        count(case when if_payment = 1 then 1 else null end)
        /count(case when if_cart = 1 then 1 else null end)
        ,3) payment_rate,
    round(
        count(case when if_refund = 1 then 1 else null end)
        /count(case when if_payment = 1 then 1 else null end)
        ,3) refund_rate
from
    tb_user_event
where
    date_format(event_time,'%Y%m') = 202110
group by
    product_id
having
    refund_rate <= 0.5
order by
    product_id asc

编辑于 2024-04-08 10:41:11 回复(1)
Mysql
牛客535608285号头像 牛客535608285号 
select product_id
    ,case when num = 0 then 0 else round(click/num,3) end ctr
    ,case when click = 0 then 0 else round(cart/click,3) end cart_rate
    ,case when cart = 0 then 0 else round(payment/cart,3) end payment_rate
    ,case when payment = 0 then 0 else round(refund/payment,3) end refund_rate
from(
    select product_id
        ,count(uid) num
        ,sum(if_click) click
        ,sum(if_cart) cart
        ,sum(if_payment) payment
        ,sum(if_refund) refund
    from tb_user_event
    where date(event_time) like '2021-10%'
    group by product_id
) a
group by product_id
having refund_rate <= 0.500
order by product_id

发表于 2023-11-24 11:33:38 回复(0)
Mysql
牛客962195832号头像 牛客962195832号
select product_id,round(if(cnt<>0,sum_click/cnt,0),3) as ctr,round(if(sum_click<>0,sum_cart/sum_click,0),3) as cart_rate,round(if(sum_cart<>0,sum_payment/sum_cart,0),3) as payment_rate,round(if(sum_payment<>0,sum_refund/sum_payment,0),3) as refund_rate
from (select product_id,sum(if_click) as sum_click,sum(if_cart) as sum_cart,sum(if_payment) as sum_payment,sum(if_refund) as sum_refund,count(product_id) as cnt
from (select product_id,if_click,if_cart,if_payment,if_refund
from tb_user_event
where date_format(event_time,"%Y-%m")="2021-10" ) as t
group by product_id) as t2
where round(if(sum_payment<>0,sum_refund/sum_payment,0),3)<=0.5
order by product_id
发表于 2023-10-31 19:50:39 回复(0)
Mysql
轩辕冥头像 轩辕冥
select
    product_id,
    round(sum(if_click) / count(*), 3) ctr,
    round(sum(if_cart) / sum(if_click <> 0), 3) cart_rate,
    round(sum(if_payment) / sum(if_cart <> 0), 3) payment_rate,
    round(sum(if_refund) / sum(if_payment <> 0), 3) refund_rate
from
    tb_user_event
where
    event_time like '2021-10%'
group by
    product_id
having
    refund_rate <= 0.5
order by
    product_id
发表于 2023-09-15 08:47:07 回复(0)
Mysql
swbl_ww头像 swbl_ww 
select
    product_id,
    round(clickNum / showNum, 3) as ctr,
    round(cartNum / clickNum, 3) as cart_rate,
    round(paymentNum / cartNum, 3) as payment_rate,
    round(refundNum / paymentNum, 3) as refund_rate
from
    (
        select
            product_id,
            count(product_id) as showNum,
            sum(if_click) as clickNum,
            sum(if_cart) as cartNum,
            sum(if_payment) as paymentNum,
            sum(if_refund) as refundNum
        from
            tb_user_event
        where
            date_format(event_time,'%Y-%m') = '2021-10'
        group by
            product_id
    ) as t
order by 
    product_id asc 
    

发表于 2023-09-14 14:31:26 回复(0)
Mysql
GuaMiao头像 GuaMiao 
select product_id, 
(case when count(uid)=0 then 0.000 else round(sum(if_click=1)/count(uid), 3) end) as ctr,
(case when sum(if_click=1)=0 then 0.000 else round(sum(if_cart=1)/sum(if_click=1), 3) end) as cart_rate,
(case when sum(if_cart=1) then 0.000 else round(sum(if_payment=1)/sum(if_cart=1), 3) end) as payment_rate,
(case when sum(if_payment=1) then 0.000 else round(sum(if_refund=1)/sum(if_payment=1), 3) end) as refund_rate
from tb_user_event
where event_time between '2021-10-01' and '2021-10-31'
group by product_id
having refund_rate <= 0.5
order by product_id;

发表于 2023-08-03 13:27:21 回复(1)
Mysql
dolen头像 dolen 
with event_log as (
select 
     uid,
     product_id,
     count(product_id)over(partition by product_id) as show_num,
     sum(if_click)over(partition by product_id) as click_num,
     sum(if_cart)over(partition by product_id) as cart_num,
     sum(if_payment)over(partition by product_id) as payment_num,
     sum(if_refund)over(partition by product_id) as refund_num,
     row_number()over(partition by product_id order by event_time) as rn
from tb_user_event where date_format(event_time,'%Y-%m')='2021-10'
)

select 
  product_id,
  if(show_num=0,0,round(click_num/show_num,3)) as ctr,
  if(click_num=0,0,round(cart_num/click_num,3)) as cart_rate,
  if(cart_num=0,0,round(payment_num/cart_num,3)) as payment_rate,
  if(payment_num=0,0,round(refund_num/payment_num,3)) as refund_rate
from event_log
where rn=1 and round(refund_num/payment_num,3) <=0.5
order by 1;

发表于 2023-08-02 19:53:38 回复(0)
Mysql
DataHenry头像 DataHenry 
select product_id	
,round(coalesce(sum(if_click)/count(uid),0),3) as ctr	
,round(coalesce(sum(if_cart)/sum(if_click),0),3) as cart_rate	
,round(coalesce(sum(if_payment)/sum(if_cart),0),3) as payment_rate	
,round(coalesce(sum(if_refund)/sum(if_payment),0),3) as refund_rate
from tb_user_event
where substr(event_time,1,7) = '2021-10'
group by product_id
having refund_rate <= 0.5
order by product_id

发表于 2023-05-30 18:41:15 回复(0)
Mysql
快乐柯基头像 快乐柯基 
select product_id,
       round(sum(if_click)/count(product_id),3) ctr,
       round(sum(if_cart)/sum(if_click),3) cart_rate,
       round(sum(if_payment)/sum(if_cart),3) payment_rate,
       round(sum(if_refund)/sum(if_payment),3) refund_rate
from tb_user_event
where year(event_time)=2021 and month(event_time)=10
group by product_id
having refund_rate<=0.5
order by product_id asc

发表于 2023-05-23 12:19:40 回复(0)
Mysql
长白山啊头像 长白山啊 
select
    product_id,
    if (
        count(product_id) = 0,
        0,
        round(sum(if_click) / count(product_id), 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
where
    year (event_time) = '2021'
    and month (event_time) = '10'

group by
    product_id
having
    refund_rate <= 0.5
order by
    product_id

发表于 2023-05-08 16:46:39 回复(0)
Mysql
Miksa头像 Miksa 
SELECT
    product_id,
    if(count(product_id)=0,0,round(sum(if_click) / count(product_id),3)) ctr,
    if(sum(if_click)=0,0,round(sum(if_cart) / sum(if_click),3)) cart_rate,
    if(sum(if_cart)=0,0,round(sum(if_payment) / sum(if_cart),3)) payment_rate,
    if(sum(if_payment)=0,0,round(sum(if_refund) / sum(if_payment),3)) refund_rate
FROM
    tb_user_event
WHERE
    DATE_FORMAT(event_time,'%Y-%m') = '2021-10'
GROUP BY 
    product_id 
having 
    refund_rate <= 0.5
ORDER BY 
    product_id

发表于 2023-04-27 16:48:08 回复(0)
Mysql
Sean Reese头像 Sean Reese
 看到有人问为什么要生成子表，以加购率举例，它是加购数/点击数，如果不生成子表的话，因为select不能直接用前一列计算的结果，所以我们再使用点击数时，无法使用计算好的数据，还需要再计算一次点击数；同理，每计算新的数据时，都要反复计算旧的数据，大量增加了代码运行的时间 
WITH T1 AS(
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 payment_cnt,
    sum(if_refund) as refund_cmt
from
    tb_user_event
WHERE
    date_format(event_time,'%Y-%m') = '2021-10'
group by
    product_id
order by
    product_id
)
SELECT
    product_id,
    round(click_cnt/show_cnt,3) as ctr,
    round(cart_cnt/click_cnt,3) as cart_rate,
    round(payment_cnt/cart_cnt,3) as payment_rate,
    round(refund_cmt/payment_cnt,3) as refund_rate
FROM T1
WHERE refund_cmt/payment_cnt <= 0.5
发表于 2023-04-15 11:05:08 回复(0)
Mysql
isabener头像 isabener 
select
    product_id,
    round(sum(if_click) / count(product_id), 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
where
    event_time >= '2021-10-01 00:00:00'
    and event_time <= '2021-10-31 23:59:59'
group by
    product_id
having
    refund_rate <= 0.5
order by
    product_id

发表于 2023-04-10 21:03:44 回复(0)
提交观点

问题信息

难度：
133条回答 1738浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号