首页 > 试题广场 >

10月的新户客单价和获客成本

[编程题]10月的新户客单价和获客成本
  • 热度指数：83972 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

商品信息表tb_product_info

id product_id
 shop_id tag in_price quantity release_time
1 8001 901 日用 60 1000 2020-01-01 10:00:00
2 8002 901 零食 140 500 2020-01-01 10:00:00
3 8003 901 零食 160 500 2020-01-01 10:00:00
4 8004 902 零食
 130 500 2020-01-01 10:00:00
（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id order_id uid event_time total_amount total_cnt status
1 301002 102 2021-10-01 11:00:00 235 2 1
2 301003 101 2021-10-02 10:00:00
 300 2 1
3 301005 104 2021-10-03 10:00:00
 160 1 1
（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id order_id product_id price cnt
1 301002 8001 85 1
2 301002
 8003
 180 1
3 301003
 8004
 140
 1
4 301003
 8003
 180 1
5 301005
 8003
 180 1

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


问题：请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。

注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。

输出示例：
示例数据的输出结果如下
avg_amount avg_cost
231.7 23.3
解释：
2021年10月有3个新用户，102的首单为301002，订单金额为235，商品总金额为85+180=265，优惠金额为30；
101的首单为301003，订单金额为300，商品总金额为140+180=320，优惠金额为20；
104的首单为301005，订单金额为160，商品总金额为180，优惠金额为20；
平均首单客单价为(235+300+160)/3=231.7，平均获客成本为(30+20+20)/3=23.3
示例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'),
  (8004, 902, '零食', 130, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
  (301003, 101, '2021-10-02 10:00:00', 300, 2, 1),
  (301005, 104, '2021-10-03 10:00:00', 160, 1, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8004, 140, 1),
  (301003, 8003, 180, 1),
  (301005, 8003, 180, 1);

输出

231.7|23.3
算法知识视频讲解
添加回答
Mysql
迟缓的小饼干1头像 迟缓的小饼干1 
select round(avg(t.total_amount),1) as avg_amount,
       round(avg(c.real_amount-t.total_amount),1) as avg_cost 
from(
select uid,
       order_id,
       total_amount,
       row_number()over (partition by uid order by event_time) as rn
from tb_order_overall 
where date_format(event_time,'%Y-%m')='2021-10'and status=1) t

join (select order_id,sum(price*cnt) as real_amount
      from tb_order_detail 
      group by 1) as c
on t.order_id=c.order_id and t.rn=1
算出的结果比答案大一点，但就是看不出哪里多算了，好心人帮忙看下
发表于 2025-07-04 17:18:35 回复(0)
Mysql
Yosangta头像 Yosangta 
with t1 as(
    select
        s1.order_id,
        total_amount temp_amount,
        sum(price*cnt) - total_amount  temp_cost
    from
        tb_order_detail s1
    join
        (
            select
                *,
                rank() over(partition by uid order by event_time) rk
            from
                tb_order_overall
            where date_format(event_time,'%Y%m') = 202110
            and     status = 1 
        ) s2
    on s1.order_id = s2.order_id 
    and rk = 1
    group by 1,2
)
select 
    round(avg(temp_amount),1) avg_amount,
    round(avg(temp_cost),1) avg_cost
from
    t1
有一个样例始终过不了，没道理吧；
发表于 2025-06-25 10:36:49 回复(0)
Mysql
在看数据的小学生很想居家头像 在看数据的小学生很想居家
请各位大佬指正。为什么计算结果和答案不一样
select round(avg(total_amount),1) as avg_amount,
round(avg(t2.price*t1.total_cnt-total_amount),1)as avg_cost
from
(
select uid,order_id,event_time,total_amount,total_cnt
from tb_order_overall
where date_format(event_time,'%Y-%m')='2021-10'
and (uid,event_time) in (select uid, min(event_time) as f_event_time
from tb_order_overall group by uid)
) as t1
left join tb_order_detail as t2
on t1.order_id=t2.order_id
发表于 2025-04-20 13:34:32 回复(0)
Mysql
牛客356924631号头像 牛客356924631号
select round(avg(total_amount),1)
      ,round(avg(discount_amt),1)
from (
        select
                a.order_id
               ,total_amount
               ,sum(price*cnt)-a.total_amount as discount_amt
        from (
                select  order_id
                       ,uid
                       ,event_time
                       ,total_amount
                from (
                        select   b.order_id
                                ,b.uid
                                ,b.event_time
                                ,b.total_amount
                                ,row_number() over(partition by b.uid order by event_time) rk
                        from (
                                select   uid
                                from tb_order_overall
                                group by uid
                                having date_format(min(event_time),'%Y-%m')='2021-10'
                             ) a
                        join tb_order_overall b
                        on a.uid = b.uid
                     ) a
                where rk =1
             ) a
        left join tb_order_detail b
        on a.order_id = b.order_id
        group by a.order_id,a.total_amount
     ) b

发表于 2025-03-19 10:02:01 回复(0)
Mysql
宋耀廷头像 宋耀廷
为什么测试不通过
with first_event_time as (
select uid, min(event_time) as first_event
from tb_order_overall
where status = 1
group by uid
having min(date_format(event_time, "%Y-%m")) >= "2021-10"
),
b as (
select
tod.order_id,
sum(tod.price * tod.cnt) as accu
from
tb_order_overall as too
join
tb_order_detail as tod on tod.order_id = too.order_id
join first_event_time as fet on too.event_time = fet.first_event
group by
tod.order_id
)
select
round(avg(too.total_amount), 1) as avg_amount, -- 所有订单的总金额平均值
round(avg(b.accu - too.total_amount), 1) as avg_cost -- 所有订单的平均成本
from b
left join tb_order_overall as too on b.order_id = too.order_id
发表于 2024-11-16 22:22:51 回复(1)
Mysql
12347h头像 12347h 
with
    t as (
        select
            t1.uid,
            sum(price * cnt) - avg(total_amount) a,
            avg(total_amount) b
        from
            (
                select
                    t3.uid,
                    date (event_time) dt,
                    t2.price,
                    t2.cnt,
                    t3.total_amount
                from
                    tb_product_info t1
                    left join tb_order_detail t2 on t1.product_id = t2.product_id
                    left join tb_order_overall t3 on t2.order_id = t3.order_id
            ) t1
            inner join (
                select
                    uid,
                    min(date (event_time)) dt
                from
                    tb_order_overall
                group by
                    uid
            ) t2 on t1.uid = t2.uid
            and t1.dt = t2.dt
        where
            year (t1.dt) = 2021
            and month (t1.dt) = 10
        group by
            t1.uid
    )
select round(avg(b),1) avg_amount,round(avg(a),1) avg_cost from t

发表于 2024-11-07 16:29:10 回复(0)
Mysql
美丽的回笼觉觉主在刷代码头像 美丽的回笼觉觉主在刷代码
请问有没有好心人看看这是出了什么错啊？测试可以过 实际运行就有误差了。 
WITH 
t1 AS (
    SELECT order_id, uid, DATE(event_time) AS fdate, total_amount,
    ROW_NUMBER() OVER (PARTITION BY uid ORDER BY event_time) AS daterank
    FROM tb_order_overall
    WHERE DATE_FORMAT(event_time, '%Y-%m') = "2021-10" 
    ),
t2 AS (
    SELECT order_id, SUM(price * cnt) AS totalpay
    FROM tb_order_detail
    GROUP BY order_id
    ),
t3 AS (
    SELECT *
    FROM t1
    JOIN t2 USING(order_id)
    WHERE daterank = 1
    )

SELECT 
ROUND(AVG(total_amount),1) AS avg_amount
,ROUND(SUM(totalpay - total_amount) / COUNT(1),1) AS avg_cost
FROM t3


发表于 2024-09-09 16:44:38 回复(0)
Mysql
dolen头像 dolen 
with tb_order_overall_ext as (
select 
t.*,
min(event_time) over(partition by uid) as min_user_dt
from tb_order_overall t
)

select 
   round(sum(if(rn=1,total_amount,0))/count(distinct uid),1) as avg_amount, -- 注意：新用户的首***均交易金额
   round((sum(price * cnt)-sum(if(rn=1,total_amount,0)))/count(distinct uid),1) as avg_cost
from (
select
   t1.*,
   t2.uid,
   t2.total_amount,
   row_number()over(partition by t1.order_id order by id ) as rn 
from tb_order_detail t1
left join tb_order_overall_ext t2
on t1.order_id = t2.order_id
where min_user_dt=event_time
  and date_format(min_user_dt,'%Y-%m')='2021-10'
) tt 

发表于 2024-08-19 17:45:53 回复(0)
Mysql
kakihala头像 kakihala 
select
round(sum(avg(订单金额))over(),1) avg_amount
,round(sum(avg(优惠金额))over(),1) avg_cost
from
(
    select
    uid
    ,date(event_time) dt
    ,round(sum(total_amount) / count(uid)) 订单金额
    ,sum(price)	商品总金额
    ,(sum(price) - round(sum(total_amount) / count(uid))) 优惠金额
    from tb_order_overall overall
    left join tb_order_detail detail on overall.order_id = detail.order_id
    left join tb_product_info info on detail.product_id = info.product_id
    where left(event_time,7) = '2021-10' and status = 1
    group by 1,2
) a
大佬们,求助。
最终select 的结果但一个使用round保留一位小数没问题，合在一起最终数字就会变大。
我在本地的数据库重新建表和使用一样的代码，结果却是正确的，为什么在牛客这里就会错误呢？



发表于 2024-08-06 16:00:58 回复(0)
Mysql
牛客238807874号头像 牛客238807874号 
with t1 as 
(
select
distinct order_id,total_amount,uid,event_time,
sum(price*cnt)over(partition by order_id) as onepcs
from tb_order_detail
join tb_order_overall using(order_id)
join tb_product_info using(product_id)
where  status = 1  and uid in(
 select uid
 from tb_order_overall
group by uid
having date_format(min(event_time),'%Y%m') = 202110 and event_time =min(event_time)
)
)
select 
round(sum(total_amount)/count(uid),1) as avg_amount,
round((sum(onepcs)-sum(total_amount))/count(uid),1) as avg_cost
from t1
发表于 2024-07-18 21:47:39 回复(0)
Mysql
牛客721994682号头像 牛客721994682号 
with t1 as(select uid,event_time,dense_rank()over(partition by uid order by event_time) cn,order_id,product_id,price,cnt,total_amount from tb_order_overall join tb_order_detail using(order_id)),
t2 as (select uid,order_id,(sum(price*cnt)-total_amount) order_realymon,total_amount from t1 where cn=1 and date_format(event_time,'%Y%m')=202110 group by uid,order_id,total_amount)
select round(avg(total_amount),1) avg_amount,round(avg(order_realymon),1) avg_cost from t2
使用聚合函数dense_rank（）over（） 对日期进行排序，并且限定时间，最后拿去排行为1的订单号，就能拿到新用户首单，接下来就比较简单，相加后，求平均数。


发表于 2024-06-07 23:56:39 回复(0)
Mysql
春招牛牛冲刺日记头像 春招牛牛冲刺日记
我是分开求解的，这是我的解法。 
select avg_amount, avg_cost
from
(
    select round(avg(total_amount),1) as avg_amount
    from
    (
        select uid, total_amount
        from
        (
            select *, rank() over(
                                    partition by uid
                                    order by event_time
                                ) as rk
            from tb_order_overall
        ) as t1
        where rk=1 and DATE_FORMAT(event_time,"%Y-%m")='2021-10'
    ) as t2
    
) as t6,
(
select round(AVG(btotal_amount-total_amount),1) as avg_cost
from (
        select order_id, uid, total_amount
        from
        (
            select *, rank() over(
                                    partition by uid
                                    order by event_time
                                ) as rk
            from tb_order_overall
        ) as t3
        where rk=1 and DATE_FORMAT(event_time,"%Y-%m")='2021-10'
    ) as t4
left join (
		select order_id, SUM(price*cnt) as btotal_amount
		from tb_order_detail
		group by order_id
		) as t5
on t4.order_id=t5.order_id

) as t7

有没有大佬知道，我在求用户首单信息的时候，我的第1种写法不能通过第3个用例，是为什么呀？求出来的客单价不一样，一个是用窗口函数OVER+where，另一个是group by作为左边的表，用join原来的表拼接。
写法1-错误 
	select round(AVG(total_amount),1) as avg_amount
	from
	(
		select t1.uid, total_amount
		from
		(
			select uid, MIN(event_time) as event_time
			from tb_order_overall
			where DATE_FORMAT(event_time,"%Y-%m")='2021-10'
			group by uid
		) as t1
		left join tb_order_overall as t2
		on t1.uid=t2.uid and t1.event_time=t2.event_time
	) as t3
写法2-正确 
    select round(avg(total_amount),1) as avg_amount
    from
    (
        select uid, total_amount
        from
        (
            select *, rank() over(
                                    partition by uid
                                    order by event_time
                                ) as rk
            from tb_order_overall
        ) as t1
        where rk=1 and DATE_FORMAT(event_time,"%Y-%m")='2021-10'
    ) as t2




发表于 2024-06-05 21:14:22 回复(0)
Mysql
FLYING_DUTCHMAN头像 FLYING_DUTCHMAN 
-- 为什么这段代码不行
select round(sum(total_amount)/count(*),1) as avg_amount
,round((sum(sum1)-sum(total_amount))/count(*),1) as avg_cost
from (select order_id,sum(price*cnt) sum1
from tb_order_detail group by order_id) t1
join( 
select order_id,total_amount,event_time
,min(event_time) over(partition by uid) min_day
,row_number() over(partition by uid order by event_time) rk
from tb_order_overall
where date_format(event_time,'%Y-%m')='2021-10'
and status=1 )t2 using(order_id)
where  event_time =min_day  and rk=1

发表于 2024-06-05 11:48:07 回复(0)
Mysql
祖拜尔实习生头像 祖拜尔实习生 
WITH OrderTable AS(
    SELECT
        oo.order_id,
        oo.uid,
        oo.event_time,
        oo.status,
        oo.total_amount,
        sum(od.price*od.cnt) sum_price,
        rank() over(partition by oo.uid order by oo.event_time) user_order_rank
    FROM
        tb_order_overall oo LEFT JOIN tb_order_detail od
        ON oo.order_id = od.order_id
    GROUP BY
        oo.order_id,
        oo.uid,
        oo.event_time,
        oo.status,
        oo.total_amount
    HAVING
        avg(oo.status) = 1
)

SELECT 
    round(avg(total_amount),1) avg_amount,
    round(avg(sum_price-total_amount),1) avg_cost
FROM 
    OrderTable
WHERE
    user_order_rank = 1
    and date_format(event_time, "%Y%m") = 202110

发表于 2024-04-11 13:02:37 回复(0)
提交观点

问题信息

难度：
183条回答 2367浏览

热门推荐

通过挑战的用户

查看代码

相关试题

10月的新户客单价和获客成本

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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