首页 > 试题广场 >

统计用户从访问到下单的转化率

[编程题]统计用户从访问到下单的转化率
  • 热度指数：38487 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
现有某个商城部分订单数据，用户访问数据，如下所示：
订单信息表：order_tb（订单id：order_id，用户id：user_id，订单金额：order_price，订单创建时间：order_time）
order_id user_id order_price order_time
101 11 380 2022-09-01 09:00:00
102 12 200 2022-09-01 10:00:00
103 13 260 2022-09-01 12:00:00
104 11 100 2022-09-02 11:00:00
105 12 150 2022-09-02 12:00:00
106 12 1200 2022-09-02 13:00:00
107 11 60 2022-09-03 09:00:00
108 13 380 2022-09-03 09:30:00
访问信息表：visit_tb（访问信息id：info_id，用户id：user_id，访问时间：visit_time，离开时间：leave_time）
info_id user_id visit_time leave_time
911 10 2022-09-01 08:00:00 2022-09-01 09:02:00
912 11 2022-09-01 08:30:00 2022-09-01 09:10:00
913 12 2022-09-01 09:50:00 2022-09-01 10:12:00
914 13 2022-09-01 11:40:00 2022-09-01 12:22:00
921 11 2022-09-02 10:30:00 2022-09-02 11:05:00
922 11 2022-09-02 12:00:00 2022-09-02 12:02:00
923 12 2022-09-02 11:40:00 2022-09-02 13:15:00
924 13 2022-09-02 09:00:00 2022-09-02 09:02:00
925 14 2022-09-02 10:00:00 2022-09-02 10:40:00
931 10 2022-09-03 09:00:00 2022-09-03 09:22:00
932 11 2022-09-03 08:30:00 2022-09-03 09:10:00
933 13 2022-09-03 09:00:00 2022-09-03 09:32:00
请统计该商城每天用户从访问到下订单的转化率。
要求输出：日期，转化率（该日下订单人数/访问人数，以百分数形式输出并四舍五入保留1位小数）
注：输出结果按照日期升序排序；
示例数据结果如下：
date cr
2022-09-01 75.0%
2022-09-02 50.0%
2022-09-03 66.7%

结果解释：
以2022-09-01为例，该日共计有user_id为10、11、12、13共计4名用户访问商城，
其中11、12、13共计3名用户下了订单，故转化率为3/4=75.0%；
其他结果同理。
示例1

输入

drop table if exists  `order_tb` ; 
CREATE TABLE `order_tb` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`order_price` int(11) NOT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_id`));
INSERT INTO order_tb VALUES(101,11,380,'2022-09-01 09:00:00'); 
INSERT INTO order_tb VALUES(102,12,200,'2022-09-01 10:00:00'); 
INSERT INTO order_tb VALUES(103,13,260,'2022-09-01 12:00:00'); 
INSERT INTO order_tb VALUES(104,11,100,'2022-09-02 11:00:00'); 
INSERT INTO order_tb VALUES(105,12,150,'2022-09-02 12:00:00'); 
INSERT INTO order_tb VALUES(106,12,1200,'2022-09-02 13:00:00'); 
INSERT INTO order_tb VALUES(107,11,60,'2022-09-03 09:00:00'); 
INSERT INTO order_tb VALUES(108,13,380,'2022-09-03 09:30:00'); 

drop table if exists  `visit_tb` ; 
CREATE TABLE `visit_tb` (
`info_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`visit_time` datetime NOT NULL,
`leave_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO visit_tb VALUES(0911,10,'2022-09-01 08:00:00','2022-09-01 09:02:00'); 
INSERT INTO visit_tb VALUES(0912,11,'2022-09-01 08:30:00','2022-09-01 09:10:00'); 
INSERT INTO visit_tb VALUES(0913,12,'2022-09-01 09:50:00','2022-09-01 10:12:00'); 
INSERT INTO visit_tb VALUES(0914,13,'2022-09-01 11:40:00','2022-09-01 12:22:00'); 
INSERT INTO visit_tb VALUES(0921,11,'2022-09-02 10:30:00','2022-09-02 11:05:00'); 
INSERT INTO visit_tb VALUES(0922,11,'2022-09-02 12:00:00','2022-09-02 12:02:00'); 
INSERT INTO visit_tb VALUES(0923,12,'2022-09-02 11:40:00','2022-09-02 13:15:00'); 
INSERT INTO visit_tb VALUES(0924,13,'2022-09-02 09:00:00','2022-09-02 09:02:00'); 
INSERT INTO visit_tb VALUES(0925,14,'2022-09-02 10:00:00','2022-09-02 10:40:00'); 
INSERT INTO visit_tb VALUES(0931,10,'2022-09-03 09:00:00','2022-09-03 09:22:00'); 
INSERT INTO visit_tb VALUES(0932,11,'2022-09-03 08:30:00','2022-09-03 09:10:00'); 
INSERT INTO visit_tb VALUES(0933,13,'2022-09-03 09:00:00','2022-09-03 09:32:00');

输出

date|cr
2022-09-01|75.0%
2022-09-02|50.0%
2022-09-03|66.7%
算法知识视频讲解
添加回答
Mysql
像初雪一样遇见你头像 像初雪一样遇见你 
select date,concat(round(avg(tag)*100,1),"%") as cr
from 
(select 
distinct date(visit_time) as date
,user_id
,case when exists(select 1 from order_tb where user_id=x.user_id and date(order_time)=date(visit_time)) then 1 else 0 end as tag
from visit_tb x) e
group by 1
order by 1

发表于 2026-01-25 14:54:33 回复(0)
Mysql
小可尼妮头像 小可尼妮
select
    date(vt.visit_time) as date,
    concat(round(count(distinct ot.user_id) / count(distinct vt.user_id) * 100, 1), '%') as cr
from    #这里我选择右连接,保留空值
    order_tb ot
    right join visit_tb vt on vt.user_id = ot.user_id and date(vt.visit_time) = date(ot.order_time)
group by
    date
发表于 2025-11-05 10:14:47 回复(0)
Mysql
迟缓的小饼干1头像 迟缓的小饼干1
select
      date(v.visit_time) as date,

     concat(round(count(distinct o.user_id)/count(distinct v.user_id)*100,1),'%')  as cr

from visit_tb as v
left join order_tb as o
  on v.user_id=o.user_id
and date(o.order_time) = date(v.visit_time)

group by date
order by date asc


发表于 2025-06-12 19:14:09 回复(0)
Mysql
牛客856824639号头像 牛客856824639号
select
    date (visit_time) as date,
    concat (
        round(
            (
                count(
                    distinct a.user_id,
                    if (date (order_time) = date (visit_time), 1, null)
                ) / count(distinct b.user_id)
            ) * 100,
            1
        ),
        '%'
    ) cr
from
    order_tb a
    right join visit_tb b on a.user_id = b.user_id
group by
    date (visit_time)
order by
    date asc
发表于 2025-03-20 15:42:59 回复(0)
Mysql
牛客963144355号头像 牛客963144355号
with order_count as
(
select date(order_time) as date,
       count(distinct user_id) as order_cnt
from order_tb
group by date(order_time)
),

visit_count as
(
select date(visit_time) as date,
       count(distinct user_id) as visit_cnt
from visit_tb
group by date(visit_time)
),

visit_order as
(
select t1.date,
       t1.visit_cnt,
       case when t2.order_cnt is null then 0 else t2.order_cnt end as order_cnt
from visit_count as t1
left join order_count as t2
on t1.date = t2.date
)

select date,
       concat(round((order_cnt/visit_cnt)*100,1),'%') as cr
from visit_order
发表于 2025-01-22 21:04:27 回复(0)
提交观点

问题信息

SQL
难度：
6条回答 1164浏览

热门推荐

相关试题

统计用户从访问到下单的转化率

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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