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

商品信息表tb_product_info

id	product_id	shop_id	tag	int_	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

祖拜尔实习生

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)

Mysql

牛客987852806号

#主要难点是，首单可能是多个单，要合并，所以提前的算好，每个单的金额。这有点费事。
select
round(
sum(total_amount)/count(distinct uid),1) as avg_amount,
round(
(sum(pt)-sum(total_amount))/count(distinct uid),1) as avg_cost
from(
select 
uid,
tl.total_amount,
date(tl.event_time) as pday,
sum(price*cnt)over(partition by tal.order_id) as pt,
row_number()over(partition by uid order by tl.event_time) as m 
from tb_order_detail tal left join tb_order_overall tl 
on tal.order_id=tl.order_id
left join tb_product_info tf on tf.product_id=tal.product_id
where
status=1
) as t 
where
t.m=1
and 
date_format(pday,"%Y%m")=202110

编辑于 2024-03-20 16:40:39 回复(0)

Mysql

牛客563312411号

首先要找出每位新用户首单订单总金额和获客成本（该订单商品单价*数量的和-订单总金额），再取平均

用户要去重

with a as 
(
select 
distinct uid
,total_amount total_amount
,sum(price*cnt) over(partition by uid)-total_amount total_cost
from tb_order_detail
join tb_order_overall using(order_id)
join tb_product_info using(product_id)
where (uid,event_time) in (select uid,min(event_time) first_event_time from tb_order_overall group by 1 
having date_format(first_event_time,'%Y-%m')='2021-10')
)
select 
round(avg(total_amount),1) avg_amount
,round(avg(total_cost),1) avg_cost
from a

navicat通过，牛客不行

with a as 
(
select 
uid
,min(date(event_time)) first_event_time
,any_value(total_amount) total_amount
,any_value(sum(price*cnt)-total_amount) total_cost
from tb_order_detail
join tb_order_overall using(order_id)
join tb_product_info using(product_id)
group by 1
having date_format(first_event_time,'%Y-%m')='2021-10'
)
select 
round(avg(total_amount),1) avg_amount
,round(avg(total_cost),1) avg_cost
from a

sum(price*cnt) over(partition by order_id)必须partition by order_id，（为啥不能是uid，我还没想清楚）

with a as 
(
select 
uid
,event_time
,total_amount total_amount
,sum(price*cnt) over(partition by order_id)-total_amount total_cost
,row_number()over(partition by uid order by event_time) rn
from tb_order_detail
join tb_order_overall using(order_id)
join tb_product_info using(product_id)
)
select 
round(avg(total_amount),1) avg_amount
,round(avg(total_cost),1) avg_cost
from a 
where rn=1
and date_format(event_time,'%Y-%m')='2021-10'

筛选条件其他写法：

where date_format(event_time,'%Y-%m') = '2021-10'
and uid in (

select uid -- 用户和其第一次购买的时间
from tb_order_overall
group by 1
having event_time=min(event_time)

)

或
where date_format(event_time,'%Y-%m') = '2021-10'
and event_time in (

select min(event_time)
from tb_order_overall
group by uid
)

或

where date_format(event_time,'%Y-%m') = '2021-10'
and event_time in (

select min(event_time) over(partiton by uid)
from tb_order_overall
)

select round(avg(total_amount),1) avg_amount,
       round(avg(total_cost),1) avg_cost
from (
      select uid, 
             total_amount,
             (sum(price*cnt) - total_amount) as total_cost
      from tb_order_detail 
      join tb_order_overall 
      using(order_id)
      where date_format(event_time,'%Y-%m') = '2021-10' 
            and (uid,event_time) in (select uid ,min(event_time)     -- 用户和其第一次购买的时间
                                     from tb_order_overall
                                     group by 1)
      group by 1,2
                  ) a

5.用吧筛选条件放到if(,uid,null)里但牛客没通过,navicat通过

或

if(event_time in (select min(event_time)over(partition by uid) from tb_order_overall) and date_format(event_time,'%Y-%m') = '2021-10',uid,null) uid

select round(sum(total_amount)/count(1),1) avg_amount,
       round(avg(cost),1) avg_cost
from (
      select 
			if(event_time in (select min(event_time) from tb_order_overall group by uid having date_format(min(event_time),'%Y-%m') = '2021-10'),uid,null) uid
			,total_amount
			,(sum(price*cnt) - total_amount) as cost
      from tb_order_detail a
      join tb_order_overall b
      using(order_id)	
			group by 1,2												
			            ) a

编辑于 2024-03-19 19:01:11 回复(0)

Mysql

要好好学习啊

with t1 as(
    select
    *,
    rank()over(partition by uid order by event_time) as rk
    from tb_order_overall
),
t2 as (
    select *             #限制条件的时候不能很早的筛选时间，因为很可能存在10月份但不是第一次购买的订单，所以用窗口函数来限制。
    from t1
    where date(event_time) like '2021-10-%' and rk =1
),
t3 as(
    select
    t2.order_id,
    t2.total_amount,
    sum(price * cnt) as pc
    from t2 left join tb_order_detail d on t2.order_id = d.order_id
    group by t2.order_id,t2.total_amount
)
select
    round(avg(total_amount),1) as avg_amount,
    round(avg(pc -total_amount),1) as avg_cost
from t3

编辑于 2024-03-08 11:13:03 回复(0)

Mysql

starry_sky

# 问题：请计算2021年10月商城里所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数）。
# 注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。

# 判断新用户和首单（排序）
with t as 
(select *
from
(select *, 
    rank() over (partition by uid order by event_time) as rank_order
from tb_order_overall) as new
# 筛选2021年10月数据
where year(new.event_time) = 2021 and month(new.event_time) = 10 and rank_order = 1)

# 计算首***均交易金额（分组）
select round(avg(data.total_amount),1) as avg_amount,
    round(avg(data.cost-data.total_amount),1) as avg_cost
from
(select uid,
    order_id,
    total_amount,
    sum(price*cnt) as cost
from t
join tb_order_detail using(order_id)
join tb_product_info using(product_id)
group by uid,order_id,total_amount) as data

编辑于 2024-03-03 18:26:31 回复(0)

Mysql

牛客605111887号

with 
t as (  # 10月新顾客总表
select info.product_id as pid, de.order_id as oid, uid, in_price, price, cnt,
    event_time, total_amount, total_cnt
from tb_product_info as info 
    inner join tb_order_detail as de on de.product_id=info.product_id
    inner join tb_order_overall as ov on ov.order_id=de.order_id
where status=1
    and date_format(event_time, '%Y-%m')='2021-10'
    and uid not in
    (select uid from tb_order_overall 
    where date_format(event_time, '%Y-%m')<'2021-10')
),
t1 as ( # 10月新顾客首单总金额
select sum(price*cnt) as first, t.uid as uid, t2.oid as oid
from t 
    inner join (select min(oid) as oid, uid from t group by uid
    )t2 on t.oid=t2.oid
group by uid, oid
)

select round(avg(total_amount), 1), round(avg(first-total_amount) ,1)
from t1 
inner join tb_order_overall as tb on oid=order_id

编辑于 2024-02-12 20:27:59 回复(0)

Mysql

又在摸鱼的做题家很喜欢唱跳ra

SELECT

ROUND(AVG(total_amount),1),

ROUND(SUM(real_amount-total_amount)/COUNT(total_amount),1)

FROM

(

SELECT

MAX(order_id) AS order_id,

uid,

MIN(event_time) AS first_tme,

MAX(total_amount) AS total_amount

FROM

tb_order_overall

GROUP BY

uid

HAVING

DATE_FORMAT (MIN(event_time), '%Y-%m') = '2021-10'

) AS t1

JOIN

(

SELECT

order_id,

SUM(price*cnt) AS real_amount

FROM tb_order_detail

GROUP BY order_id

) AS t2

ON (t1.order_id = t2.order_id)
这个结果AVG以后得到的答案是错的，但是我单查询表连接以后的结果得到的数和例题是一样的，我大概知道问题处在t1表的group by上，但是没太想明白问题出在哪，求大佬赐教

发表于 2023-12-29 09:48:02 回复(0)

Mysql

等灯等灯2024

WITH t2 AS (
SELECT t1.*,tbo.`order_id`,tbo.`total_amount` FROM (
SELECT uid,MIN(DATE(event_time)) first_order FROM tb_order_overall GROUP BY uid)t1
RIGHT JOIN tb_order_overall tbo ON t1.uid=tbo.`uid`
WHERE first_order=DATE(event_time) AND first_order BETWEEN '2021-10-01' AND '2021-10-31'
),
t3 AS (
SELECT order_id,SUM(price*cnt) sum_price FROM tb_order_detail GROUP BY order_id
)
SELECT ROUND(AVG(total_amount),1) avg_amount,ROUND((sum(sum_price)-SUM(total_amount))/COUNT(*),1) avg_cost 
FROM t2 JOIN t3 ON t2.order_id=t3.order_id;
这题目自己做得有点混乱，逻辑不是很好。

发表于 2023-12-18 23:11:39 回复(0)

Mysql

yy_wang

求问这段代码哪里有问题55，自测是正确的，但是提交显示的答案不对
（看了子查询的表，显示的数据都没问题）

select
    round(avg(total_amount),1) as '客单价',
    round(avg(total_value-total_amount),1) as '平均获客成本'
from (
    select
        uid,
        order_id,
        total_amount,
        event_time,
        sum(price*cnt) over(partition by uid,order_id order by event_time) as total_value,
        row_number() over(partition by uid,order_id order by event_time) as ranking
    from tb_order_overall
    join tb_order_detail using (order_id)
    where status = 1) t # 每个用户每个订单的排序
where date_format(event_time,'%Y-%m') = '2021-10'
and ranking  = 1

发表于 2023-11-24 22:46:31 回复(1)

Mysql

牛客895410396号

-- 1、获取每单的信息：*，第几单，商品总金额（吊牌价）
with tmp as (
select
    order_id,
    uid,
    event_time,
    total_amount,
    rank() over (partition by uid order by event_time asc) as order_ranking,
    (
    select sum(t2.cnt*t2.price)
    from tb_order_detail as t2
    where t2.order_id = t1.order_id
    ) as origin_price
from tb_order_overall as t1)

-- 2、筛选出10月的新客户，计算指标
select
    round(avg(total_amount),1) as avg_total_amount,
    round(avg(origin_price - total_amount),1) as avg_cost
from tmp
where 
    date_format(event_time, '%Y-%m') = '2021-10'
    and order_ranking = 1;

发表于 2023-11-17 18:40:10 回复(0)

Mysql

在理财的六边形战士很调皮

客单价和获客成本

思路：

1.先找新客首单

2.筛选出新客首单、规定时间

3.左连接明细表

4.求平均客单价和平均补贴价格

select
    round(avg(total_amount), 1) avg_amount,
    round(avg(sum_amount - total_amount), 1) avg_cost
from
    (
        select
            order_id,
            total_amount
        from
            (
                select
                    *,
                    min(event_time) over (
                        partition by
                            uid
                    ) new_time#新客下单时间
                from
                    tb_order_overall
            ) a
        where
            DATE_FORMAT (event_time, '%Y-%m') = '2021-10'
            and event_time = a.new_time#筛选新客下单时间
    ) b
    left join (
        select
            order_id,
            sum(price * cnt) sum_amount
        from
            tb_order_detail
        group by
            order_id
    ) c on b.order_id = c.order_id

发表于 2023-11-02 15:50:42 回复(0)

Mysql

南瓜阳明

为什么select 出来是 231.7|-23.3

把 round(sum(b.total_amount - c.s_p)/count(*),1)
改为
round(sum(- b.total_amount + c.s_p)/count(*),1)

结果就是 240.0|20.0

select round(sum(b.total_amount)/count(*), 1) as avg_amount, round(sum(b.total_amount - c.s_p)/count(*),1) as avg_cost
from
(select *
from
(select uid, order_id, event_time, row_number() over(partition by uid) as rn_t, total_amount
from tb_order_overall
where substr(event_time,1,7) = '2021-10' and status = 1
group by uid, order_id, event_time, total_amount
) as a
where a.rn_t = 1
) as b
join
(
select order_id, sum(price) as s_p
from tb_order_detail
group by order_id
) as c
on b.order_id = c.order_id

发表于 2023-10-30 10:37:57 回复(1)

Mysql

牛客245738606号

with a as
(SELECT
tb_order_overall.order_id oid,
tb_order_overall.uid  uid,
tb_order_overall.total_amount tmt,
tb_order_detail.price  pc,
tb_order_overall.event_time et,
tb_order_detail.cnt cnt,
min(tb_order_overall.event_time) over(PARTITION by tb_order_overall.uid) mt,
sum(tb_order_detail.price*tb_order_detail.cnt) over(PARTITION by tb_order_detail.order_id) ssm
FROM
tb_order_overall left join tb_order_detail on tb_order_overall.order_id=tb_order_detail.order_id
)
SELECT
ROUND(sum(DISTINCT tmt)/count(DISTINCT uid),1),
ROUND((sum(DISTINCT ssm)-sum(DISTINCT tmt))/count(DISTINCT uid),1)
FROM
a
WHERE
et=mt and
DATE_FORMAT(mt,'%Y%m')='202110'

利用窗口函数分组计算每个订单的总金额、每个用户的最小时间，要注意新用户定义，最后再进行10月份筛选。前面先进行10月订单筛选报错（存在9月份的新用户，10月份下单的情况，导致误判）

发表于 2023-09-27 16:27:29 回复(0)

Mysql

程程*

select

round(avg(total_amount),1) avg_amount,round(avg(cost),1) avg_cost

from(

select tod.order_id,uid,total_amount,sum(price*cnt) - total_amount cost

from tb_order_detail tod

join tb_order_overall too

on tod.order_id = too.order_id

where date_format(event_time,'%Y%m') = 202110

and status = 1

and exists (select uid,min(event_time) from tb_order_overall too2

where too.uid = too2.uid and too.event_time= too2.event_time

group by too2.uid

having date_format(date(min(event_time)),'%Y%m') = 202110)

group by tod.order_id,uid,total_amount

)tmp

;

有没有大神告诉我一下，这样为什么是不行的？

发表于 2023-09-27 16:16:12 回复(1)

Mysql

War张

with t as (
    select
        uid,
        event_time,
        total_amount,
        price,
        cnt,
        rank() over(partition by uid order by event_time) rk
    from tb_order_overall oo
    join tb_order_detail od
    on oo.order_id = od.order_id
)
select distinct
    round(avg(avg(total_amount)) over(),1) avg_amount,
    round(avg(sum(price * cnt) - avg(total_amount)) over(),1) avg_cost
from t
where date_format(event_time,'%Y-%m') = '2021-10' and rk = 1
group by uid

发表于 2023-09-14 21:34:05 回复(0)

提交观点

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题