首页 > 试题广场 >

零食类商品中复购率top3高的商品

[编程题]零食类商品中复购率top3高的商品
  • 热度指数：105746 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

商品信息表tb_product_info


（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）


订单总表tb_order_overall


（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）


订单明细表tb_order_detail

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）


场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；

  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-订单状态为1表示已付款；

  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。


问题：请统计零食类商品中复购率top3高的商品。

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
       此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
       近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

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

解释：
商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；
商品8002被101购买了两次，被105购买了1次，复购率0.500；
商品8003被102购买两次，被101和105各购买1次，复购率为0.333。
示例1

输入

DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '零食', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-09-30 10:00:00', 140, 1, 1),
  (301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
  (301011, 102, '2021-10-31 11:00:00', 250, 2, 1),
  (301003, 101, '2021-11-02 10:00:00', 300, 2, 1),
  (301013, 105, '2021-11-02 10:00:00', 300, 2, 1),
  (301005, 104, '2021-11-03 10:00:00', 170, 1, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8002, 150, 1),
  (301011, 8003, 200, 1),
  (301011, 8001, 80, 1),
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8002, 140, 1),
  (301003, 8003, 180, 1),
  (301013, 8002, 140, 2),
  (301005, 8003, 180, 1);

输出

8001|1.000
8002|0.500
8003|0.333
算法知识视频讲解
添加回答
Mysql
飞越地平线1头像 飞越地平线1
复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序


我不知道我是不是自己有理解问题，我看很多通过代码是直接在where后
AND event_time >= (SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)FROM tb_order_overall)

但这段函数相当于是取了全表的最大时间，然后减去89天

但题目是每个商品下的uid，然后根据每个uid看他们的90天内复购
假设 uid1的最大时间是2021年9月10号，uid2的最大时间是2021年10月5号，每个uid的最大时间和最小时间都是不同的

反正我是没写出来 逻辑太乱了
发表于 2025-06-17 23:23:37 回复(0)
Mysql
牛客812823537号头像 牛客812823537号
#先筛选出购买表，再认定购买次数大于1的复购人员
with t2 as (
    select product_id,uid,count(*) as cnt
    from (
select b.product_id,uid,event_time
from tb_order_overall a
left join tb_order_detail b on a.order_id=b.order_id
left join tb_product_info c on c.product_id=b.product_id
where tag='零食' and status!=0)t1
where datediff((select max(event_time) from tb_order_overall),event_time) between 0 and 89
group by product_id,uid
)

select product_id,round(sum(if(cnt>1,1,0))/count(uid),3) as repurchase_rate
from t2
group by product_id
order by repurchase_rate desc,product_id asc
limit 3
发表于 2025-02-24 16:44:39 回复(0)
Mysql
牛客192106770号头像 牛客192106770号
with t as (
    select *
    from tb_order_overall
    where timestampdiff(day,event_time,(
            select max(event_time)
    from tb_order_overall
    ))<=89  and status = 1
)

select product_id,round(sum(if(num>=2,1,0))/count(product_id),3) as repurchase_rate
from(
    select uid,product_id,count(*) as num
from t right join tb_order_detail tod on t.order_id = tod.order_id
where product_id in (
    select product_id
    from tb_product_info
    where tag = '零食'
)
group by uid,product_id
)t1
group by product_id
order by repurchase_rate desc,product_id
limit 3
发表于 2025-01-02 11:55:19 回复(0)
Mysql
milknesy头像 milknesy
复购率的核心：
复购率=（总人数 - 首次购买人数）/（首次购买人数）
group by 产品种类 
select 
product_id,
round((count(uid) - count(distinct uid))/count(distinct uid),3) as cnt
    from(
    select
    uid,
    product_id,
    date(event_time) dt
    from(
        tb_product_info join tb_order_detail using(product_id) join tb_order_overall using(order_id)
    )
    where tag = '零食' and timestampdiff(day,date(event_time),date((select max(date(event_time)) from tb_order_overall))) < 90 and status = 1) a
group by product_id
order by cnt DESC,product_id ASC
limit 3


发表于 2024-12-22 19:52:14 回复(0)
Mysql
牛客399703757号头像 牛客399703757号
select
product_id
,round(fg/zs,3) as repurchase_rate
from
(select
product_id
,count(distinct product_id) as fg
,zs
from
    (select
    product_id
    ,count(distinct uid) as zs
    from
    (select
    product_id
    ,uid
    from tb_order_detail
    left join tb_order_overall
    using(order_id)
    ) a
    group by product_id
    )c  
left join
    (select
    product_id
    ,uid
    ,count(product_id) as fg
    from
    (select
    uid
    ,product_id
    ,datediff(event_time,a)
    from
    (select
    uid
    ,status
    ,product_id
    ,event_time
    ,min(event_time)over(partition by uid,product_id) as a
    ,count(event_time)over(partition by uid,product_id) as b
    from tb_order_detail
    left join tb_order_overall
    using(order_id)
    ) a
    where b>=2 and datediff(event_time,a)>0 and datediff(event_time,a)<=90 and status=1) a
    group by product_id,uid
) a  
using(product_id)
group by product_id) a
order by repurchase_rate desc
limit 3
发表于 2024-08-02 15:27:33 回复(0)
Mysql
我要offer45458头像 我要offer45458 
SELECT 
    product_id,
    ROUND(SUM(IF(uid_buy_count>1,1,0))/COUNT(*),3) AS repurchase_rate

FROM (

SELECT 
    product_id,
    uid,
    COUNT(*) AS uid_buy_count
FROM tb_order_detail tod
JOIN tb_order_overall tov USING(order_id)
JOIN tb_product_info tpi USING(product_id)
WHERE event_time >= (
    SELECT 
    DATE_SUB(MAX(event_time),INTERVAL 89 DAY)
    ) AND tag = "零食" AND status = 1
GROUP BY uid,product_id
)t
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id 
LIMIT 3;
为什么有一组不能通过，感觉没问题🤔
发表于 2024-07-26 16:15:04 回复(0)
Mysql
牛客721994682号头像 牛客721994682号

如果我们要使用最近的event_time作为参考点来计算近90天内的购买数据，并且将包含event_time的那一天也算作一天，我们需要调整查询中与日期相关的部分。我们可以使用event_time字段的最大值来代替当前日期，然后从这个最大日期往前计算90天。

以下是调整后的SQL查询步骤：

  1. 筛选出所有标记为零食类别的商品。
  2. 从订单明细表中统计从最大event_time往前90天内购买这些商品的用户数据。
  3. 对于每个商品，计算至少购买两次的用户数。
  4. 对于每个商品，计算购买过的总用户数。
  5. 计算每个商品的复购率。
  6. 按复购率倒序、商品ID升序排序。
  7. 限制结果为复购率最高的前3个商品。

以下是完成这些步骤的SQL查询： 

 WITH recent_purchases AS (
  SELECT
    product_id,
    uid,
    COUNT(a.order_id) AS purchase_count
  FROM
    tb_order_detail AS a
  JOIN
    tb_order_overall USING(order_id)
  WHERE
    STATUS = 1 AND
    event_time >= (SELECT MAX(event_time) FROM tb_order_overall) - INTERVAL 90 DAY AND
    product_id IN (SELECT product_id FROM tb_product_info WHERE tag = '零食')
  GROUP BY
    product_id,
    uid
),
repurchasers AS (
  SELECT
    product_id,
    COUNT(*) AS repurchaser_count
  FROM
    recent_purchases
  WHERE
    purchase_count > 1
  GROUP BY
    product_id
),
total_purchasers AS (
  SELECT
    product_id,
    COUNT(DISTINCT uid) AS total_purchaser_count
  FROM
    recent_purchases
  GROUP BY
    product_id
)
SELECT
  rp.product_id,
  ROUND(rp.repurchaser_count / tp.total_purchaser_count, 3) AS repurchase_rate
FROM
  repurchasers rp
JOIN
  total_purchasers tp ON rp.product_id = tp.product_id
ORDER BY
  repurchase_rate DESC,
  rp.product_id ASC
LIMIT 3;
在这个查询中，我们使用子查询 (SELECT MAX(event_time) FROM tb_order_overall) 来获取最大的event_time，然后从这个日期往前计算90天。这样，我们就可以确保包含event_time的那一天也被计算在内，并且是以最近的订单日期为基准来统计的90天内购买数据。

发表于 2024-05-19 00:27:52 回复(0)
Mysql
牛客877087806号头像 牛客877087806号 
select product_id,
round(sum(case when cs=2 then 1 else 0 end)/count(distinct uid),3) repurchase_rate
from 
(select t2.product_id,uid,event_time,
ROW_NUMBER() over(partition by product_id,uid order by event_time) cs
from (select order_id,uid,event_time from tb_order_overall where status=1) t1
join (select order_id,product_id,cnt from tb_order_detail) t2
on t1.order_id=t2.order_id
join (select product_id from tb_product_info where tag='零食')t3 on t2.product_id=t3.product_id
where date(event_time)>=(select date_sub(date(max(event_time)),interval 89 day) from tb_order_overall))t4
group by product_id
order by repurchase_rate desc,product_id 
limit 3

发表于 2024-05-09 15:20:40 回复(0)
Mysql
烟雨潇潇头像 烟雨潇潇 
# t1 消费者 商品 近90天购买次数 限制零食
with t1 as (
select uid, det.product_id, count(*) cnt
from tb_order_detail det
inner join tb_order_overall ord on det.order_id = ord.order_id 
inner join tb_product_info pro on det.product_id = pro.product_id 
where tag = '零食'
    and datediff((select max(event_time) from tb_order_overall),event_time) <= 89
    and status = 1
group by uid, det.product_id)

select product_id, round(count(case when cnt>1 then uid else null end)/count(*),3) repurchase_rate
from t1
group by product_id
order by repurchase_rate desc, product_id
limit 3

发表于 2024-05-04 10:53:54 回复(0)
Mysql
jhggnnadsj头像 jhggnnadsj 
SELECT 
    table1.product_id
    , round(count(distinct table2.uid)/count(distinct table1.uid),3) as repurchase_rate
FROM 
    (SELECT 
        t3.product_id
        , uid
        , count(*) as person_cnt_total
    FROM 
        tb_order_overall as t2 
        JOIN tb_order_detail as t3 USING(order_id)
        JOIN tb_product_info as t1 USING(product_id)
    WHERE 
        t1.tag = '零食' AND status = 1
    GROUP BY 
        t3.product_id , uid) as table1
    LEFT JOIN
    (
    SELECT 
        t3.product_id
        , uid
        , count(*) as person_cnt_90d
    FROM 
        tb_order_overall as t2 
        JOIN tb_order_detail as t3 USING(order_id)
        JOIN tb_product_info as t1 USING(product_id)
    WHERE 
        t1.tag = '零食' 
        AND datediff((select max(event_time) from tb_order_overall),date(event_time)) between 0 and 89
        AND status = 1
    GROUP BY 
        t3.product_id , uid 
    HAVING 
        count(uid) >= 2
    ) as table2
    ON table1.product_id = table2.product_id
GROUP BY  table1.product_id
ORDER BY 
    repurchase_rate desc , product_id asc
LIMIT 3;

编辑于 2024-04-03 14:17:01 回复(0)
Mysql
fayespike头像 fayespike 
with t1 as 
(select b.product_id,uid,count(uid) cnp
from tb_order_overall a
left join tb_order_detail b on a.order_id = b.order_id
left join tb_product_info c on b.product_id = c.product_id
where tag = '零食'
and date(event_time) > (select date_sub(date(max(event_time)),interval 90 day) from tb_order_overall)
group by b.product_id,uid
order by b.product_id,uid)

select product_id,round((sum(if(cnp>=2,1,0))/count(product_id)),3) rt
from t1
group by product_id
order by rt desc,product_id
limit 3
1.遇事不决先连表，把筛选条件写上 
2.想想题目要求的什么：90天内，所有的购买
3.把产品和人员分组再给人员计数，就可以看到哪个产品，哪个人购买了几次
4.对于每个产品：购买两次以上的人数/购买总人数
5.排序，限制行数
编辑于 2024-03-25 13:31:36 回复(0)
Mysql
starry_sky头像 starry_sky 
# 问题：请统计零食类商品中复购率top3高的商品。
# 注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
# 此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
# 近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序


# 1 统计所有商品的复购率，并筛选零食类商品

# 首先统计90天内是否购买它至少两次（分组函数不能嵌套）
# 然后统计90天内购买总人数
select new.product_id,
    round(sum(repurchase_rate)/count(distinct uid),3) as repurchase_rate
from
(select product_id, 
    uid,
    if(count(uid)>=2,1,0) as repurchase_rate
from tb_product_info 
join tb_order_detail using(product_id)
join tb_order_overall using(order_id)
where event_time >= (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
        ) and tag = "零食"
group by product_id,uid) as new
group by new.product_id
order by repurchase_rate desc, product_id asc
# 2 提取复购率top3高的商品
limit 3

编辑于 2024-03-03 11:25:26 回复(0)
Mysql
牛客605111887号头像 牛客605111887号 
with 
t as ( # 筛选符合条件的总表
select info.product_id as pid, ov.order_id as oid, uid, event_time, md
from tb_product_info as info 
    inner join tb_order_detail as de on info.product_id=de.product_id
    inner join tb_order_overall as ov on ov.order_id=de.order_id,
    (select max(event_time) as md from tb_order_overall)t
where status=1
    and timestampdiff(day, event_time, md) < 90 
    # 这里如果用datediff有个例子过不了，害我花了好长时间。。。
    and tag='零食'),
t1 as ( # 购买总人数
select pid, count(distinct uid) as cnt_all
from t 
group by pid),
t2 as ( # 购买两次人数
select pid, count(distinct uid) as cnt_t
from t 
group by pid, uid 
having count(uid)>1)

select pid, 
case when round(cnt_t/cnt_all, 3) is not null then round(cnt_t/cnt_all, 3)
else 0.000 end
as r 
from t1 left join t2 using(pid)
order by r desc, pid
limit 3
datediff和timestampdiff直接卡我好久
发表于 2024-02-12 19:23:43 回复(0)
Mysql
牛客987852806号头像 牛客987852806号
我就纳闷了，包含90，不是datediff((select max(date(event_time)) from tb_order_overall),date(tal.event_time))<=90 就是错的，
datediff((select max(date(event_time)) from tb_order_overall),date(tal.event_time))<90就是对了，服了
编辑于 2023-12-22 16:53:32 回复(0)
Mysql
等灯等灯2024头像 等灯等灯2024 
WITH t1 AS (
SELECT tbd.*,tbo.uid,tbo.`status`,DATE_FORMAT(tbo.`event_time`,'%Y-%m-%d') dt,tbi.`tag` FROM tb_order_detail tbd 
JOIN tb_order_overall tbo ON tbd.`order_id`=tbo.`order_id`
JOIN tb_product_info tbi ON tbd.`product_id`=tbi.`product_id`
WHERE tag='零食' AND DATEDIFF((SELECT DATE_FORMAT(MAX(event_time),'%Y-%m-%d') FROM  tb_order_overall), DATE_FORMAT(tbo.`event_time`,'%Y-%m-%d') )<90
AND STATUS=1
),
t2 AS (
SELECT product_id,uid,COUNT(uid) num FROM t1 GROUP BY product_id,uid 
)

SELECT  product_id,
ROUND(SUM(IF(num>=2,1,0))/COUNT(uid),3) repurchase_rate
FROM t2 GROUP BY product_id ORDER BY repurchase_rate DESC,product_id ASC  limit 3;
我感觉用两张临时表是最简单、好理解的

编辑于 2023-12-17 22:54:32 回复(0)
Mysql
一睡不起的曲奇饼干头像 一睡不起的曲奇饼干
复购率vs留存率
  • 留存率其实需要的信息更多，即：需要保证第一天有记录的“新用户”在第二天仍要有；所以必须要把“新用户第一次登录时间”+“所有登录时间”两个表连接起来，再限制两个时间的差值，才能找到目标；
  • 复购率则只需要知道每个产品、每个用户(group by product_id,uid)的购买次数，筛选出每个产品有过两次及以上的购买次数（case when）的用户及总购买人数，所以不需要连接两个表； 

#step1：筛选出符合题目条件的数据（近90天，“零食”）
with t as (select 
oo.uid,date_format(oo.event_time,'%Y-%m-%d') sday,od.product_id
from tb_order_overall oo left join tb_order_detail od
on oo.order_id=od.order_id
left join tb_product_info pi
on od.product_id=pi.product_id
where pi.tag="零食" and oo.status=1 
and datediff((select max(date_format(event_time,'%Y-%m-%d')) from tb_order_overall),date_format(oo.event_time,'%Y-%m-%d'))<=90)

#step3：计算每个产品复购的人数（购买次数>=2)与购买总人数(count uid）的比值
select a.product_id,
round(sum(case when cnt>=2 then 1 else 0 end)/count(uid),3) rate #step2：计算每个产品下每个用户的购买次数
from(
    select product_id,uid,count(*) cnt from t group by product_id,uid
    )a
group by a.product_id
order by rate desc limit 3



发表于 2023-12-01 19:59:10 回复(0)
Mysql
War张头像 War张 
select distinct 
    od.product_id,
    round(avg(if(count(uid) > 1,1,0)) over(partition by od.product_id),3) repurchase_rate
from tb_product_info pi
join tb_order_detail od
on pi.product_id = od.product_id
join tb_order_overall oo
on od.order_id = oo.order_id
where tag = '零食'
and date(event_time) > date_sub((select date(max(event_time)) from tb_order_overall),interval 90 day)
group by od.product_id,oo.uid
order by repurchase_rate desc,od.product_id
limit 3;

发表于 2023-09-14 20:35:47 回复(0)
Mysql
dolen头像 dolen 
with order_main as (
select 
uid,
order_id,
event_time,
sum(total_amount)over(partition by order_id) as order_stat, -- 判断是否退货
datediff(date(max(event_time)over()),date(event_time)) as dff_days -- 最近90天
from tb_order_overall
)

select 
product_id,
round(count(distinct if(cnt>1,uid,null))/count(distinct uid ),3) as  repurchase_rate
from
(
select 
t1.uid,
t2.product_id,
count(t2.product_id )over(partition by t1.uid,t2.product_id ) as cnt
 from order_main t1 
left join tb_order_detail t2
on t1.order_id = t2.order_id
inner join tb_product_info t3
on t2.product_id= t3.product_id
where t1.dff_days< 90  -- 最近90天
  and t1.order_stat >0 -- 取未退货数据
  and t3.tag='零食'
) tt
group by product_id
order by 2 desc ,1 asc
limit 3

发表于 2023-08-07 00:10:53 回复(1)
提交观点

问题信息

难度：
144条回答 2401浏览

热门推荐

通过挑战的用户

查看代码

相关试题

零食类商品中复购率top3高的商品

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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