SQL(面试实战03)

最新推荐文章于 2023-12-10 19:10:56 发布
最新推荐文章于 2023-12-10 19:10:56 发布
阅读量1.6k 收藏 15
点赞数 2
分类专栏： mysql 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44464850/article/details/125190102
版权

1.计算商城中2021年每月的GMV

现有订单总表tb_order_overall

idorder_iduidevent_timetotal_amounttotal_cntstatus
13010011012021-10-01 10:00:001590021
23010021012021-10-01 11:00:001590021
33010031022021-10-02 10:00:003450080
43010041032021-10-12 10:00:004350091
53010051052021-11-01 10:00:003190071
63010061022021-11-02 10:00:002450061
73010071022021-11-03 10:00:00-2450062
83010081042021-11-04 10:00:0055500120

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

场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；
  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；
  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。

注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。

输出示例：

示例数据输出如下：

monthGMV
2021-10109800
2021-11111900

解释：

2021年10月有3笔已付款的订单，1笔未付款订单，总交易金额为109800；2021年11月有2笔已付款订单，1笔未付款订单，

总交易金额为111900（还有1笔退款订单由于已计算了付款的订单金额，无需计算在GMV中）。

示例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;

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-10-01 10:00:00', 15900, 2, 1),
  (301002, 101, '2021-10-01 11:00:00', 15900, 2, 1),
  (301003, 102, '2021-10-02 10:00:00', 34500, 8, 0),
  (301004, 103, '2021-10-12 10:00:00', 43500, 9, 1),
  (301005, 105, '2021-11-01 10:00:00', 31900, 7, 1),
  (301006, 102, '2021-11-02 10:00:00', 24500, 6, 1),
  (391007, 102, '2021-11-03 10:00:00', -24500, 6, 2),
  (301008, 104, '2021-11-04 10:00:00', 55500, 12, 0);
输出

2021-10|109800
2021-11|111900

题解
select
date_format(event_time,'%Y-%m') month,
round(sum(total_amount),0) GMV
from tb_order_overall
where year(event_time)='2021' and status=0 or status=1
group by date_format(event_time,'%Y-%m')
having GMV >100000
order by GMV

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

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

iduidproduct_idevent_timeif_clickif_cartif_paymentif_refund
110180012021-10-01 10:00:000000
210280012021-10-01 10:00:001000
310380012021-10-01 10:00:001100
410480012021-10-02 10:00:001110
510580012021-10-02 10:00:001110
610180022021-10-03 10:00:001110
710980012021-10-04 10:00:001111

（uid-用户ID, product_id-商品ID, event_time-行为时间, if_click-是否点击, if_cart-是否加购物车, if_payment-是否付款, if_refund-是否退货退款）

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

注：

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

输出示例：

示例数据的输出结果如下

product_idctrcart_ratepayment_raterefund_rate
80010.8330.8000.7500.333
80021.0001.0001.0000.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

题解
select 
	product_id,
	round(count_click/count_product,3) ctr,
	round(count_cart/count_click,3) cart_rate,
	round(count_payment/count_cart,3) payment_rate,
	round(count_refund/count_payment,3) refund_rate
from 
(
	select 
	product_id,
	count(*) count_product,
	sum(if_click) count_click,
	sum(if_cart) count_cart,
	sum(if_payment) count_payment,
	sum(if_refund)count_refund
	from tb_user_event
	where DATE_FORMAT(event_time,'%Y-%m')='2021-10'
	group by product_id
)t_product
having refund_rate<=0.5 
order by product_id

3. 某店铺的各商品毛利率及店铺整体毛利率

商品信息表tb_product_info

idproduct_idshop_idtagin_pricequantityrelease_time
18001901家电60001002020-01-01 10:00:00
28002902家电12000502020-01-01 10:00:00
380039013C数码12000502020-01-01 10:00:00

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

订单总表tb_order_overall

idorder_iduidevent_timetotal_amounttotal_cntstatus
13010011012021-10-01 10:00:003000031
23010021022021-10-01 11:00:002390021
33010031032021-10-02 10:00:003100021

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

订单明细表tb_order_detail

idorder_idproduct_idpricecnt
1301001800185002
23010018002150001
3301002800185001
43010028002160001
53010038002140001
63010038003180001

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

场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1表示已付款；
  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

注：商品毛利率=(1-进价/平均单件售价)*100%；

店铺毛利率=(1-总进价成本/总销售收入)*100%。

结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

输出示例：

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

product_idprofit_rate
店铺汇总31.0%
800129.4%
800333.3%

解释：

店铺901有两件商品8001和8003；8001售出了3件，销售总额为25500，进价总额为18000，毛利率为1-18000/25500=29.4%，8003售出了1件，售价为18000，进价为12000，毛利率为33.3%；

店铺卖出的这4件商品总销售额为43500，总进价为30000，毛利率为1-30000/43500=31.0%

示例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;

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-10-01 10:00:00', 30000, 3, 1),
  (301002, 102, '2021-10-01 11:00:00', 23900, 2, 1),
  (301003, 103, '2021-10-02 10:00:00', 31000, 2, 1);

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, '家电', 6000, 100, '2020-01-01 10:00:00'),
  (8002, 902, '家电', 12000, 50, '2020-01-01 10:00:00'),
  (8003, 901, '3C数码', 12000, 50, '2020-01-01 10:00:00');

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8001, 8500, 2),
  (301001, 8002, 15000, 1),
  (301002, 8001, 8500, 1),
  (301002, 8002, 16000, 1),
  (301003, 8002, 14000, 1),
  (301003, 8003, 18000, 1);
输出

店铺汇总|31.0%
8001|29.4%
8003|33.3%

题解
方式一：
select *
from 
(
	select 
	if(product_id is not null ,product_id,'店铺汇总') product_id,
	concat(round((1-sum(cnt_in_price)/sum(cnt_price))*100,1),"%")profit_rate
	from 
	(
		select product_id,in_price*cnt cnt_in_price,price*cnt cnt_price
		from tb_product_info
		inner join tb_order_detail
		using(product_id)
		inner join tb_order_overall
		using(order_id)
		where date_format(event_time,"%Y-%m")>="2021-10"
        and shop_id="901" and status=1
	)t_product
	group by product_id
	with rollup
	order by product_id 
)t
where product_id ="店铺汇总" or profit_rate>24.9

方式二：
select 
'店铺汇总' as pproduct_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from 
tb_product_info t
join tb_order_overall t1 
join tb_order_detail t2 
on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901  and date(event_time)>='20211001'
union
select *
from
(
select 
t.product_id as pproduct_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info t 
    join tb_order_overall t1
    join tb_order_detail t2 
    on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901  
    and date(event_time)>='20211001'
group by t.product_id
having 1-sum(in_price*cnt)/sum(price*cnt)>0.249
order by pproduct_id) t

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

商品信息表tb_product_info

idproduct_idshop_idtagint_quantityrelease_time
18001901零食6010002020-01-01 10:00:00
28002901零食1405002020-01-01 10:00:00
38003901零食1605002020-01-01 10:00:00

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

订单总表tb_order_overall

idorder_iduidevent_timetotal_amounttotal_cntstatus
13010011012021-09-30 10:00:0014011
23010021022021-10-01 11:00:0023521
33010111022021-10-31 11:00:0025021
43010031012021-10-02 10:00:0030021
53010131052021-10-02 10:00:0030021
63010051042021-10-03 10:00:0017011

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

订单明细表tb_order_detail

idorder_idproduct_idpricecnt
130100180021501
230101180032001
33010118001801
43010028001851
530100280031801
630100380021401
730100380031801
830101380021402
930100580031801

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

场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态-****订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-****订单状态为1表示已付款；
  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。

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

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

输出示例：

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

product_idrepurchase_rate
80011.000
80020.500
80030.333

解释：

商品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

思路
1.计算每个用户对每个商品是否复购：
内连接多表：
tb_order_detail 
JOIN tb_order_overall USING(order_id) 
JOIN tb_product_info USING(product_id)
2.筛选零食类商品：WHERE tag="零食"
3.筛选近90天的记录：
计算最小允许日期：DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
筛选：event_time >= (SELECT ... FROM tb_order_overall)
按用户和商品分组：GROUP BY uid, product_id
4.计算是否复购：IF(COUNT(event_time)>1, 1, 0) as repurchase
5.按商品分组：GROUP BY product_id
6.计算复购率：
SUM(repurchase) / COUNT(repurchase) as repurchase_rate
保留3位小数：ROUND(x, 3)
题解
SELECT product_id,
    ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
FROM (
    SELECT uid, product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    JOIN tb_product_info USING(product_id)
    WHERE tag="零食" AND event_time >= (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
    GROUP BY uid, product_id
) as t_uid_product_info
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;

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

商品信息表tb_product_info

idproduct_idshop_idtagint_quantityrelease_time
18001901日用6010002020-01-01 10:00:00
28002901零食1405002020-01-01 10:00:00
38003901零食1605002020-01-01 10:00:00
48004902零食1305002020-01-01 10:00:00

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

订单总表tb_order_overall

idorder_iduidevent_timetotal_amounttotal_cntstatus
13010021022021-10-01 11:00:0023521
23010031012021-10-02 10:00:0030021
33010051042021-10-03 10:00:0016011

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

订单明细表tb_order_detail

idorder_idproduct_idpricecnt
13010028001851
230100280031801
330100380041401
430100380031801
530100580031801

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

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

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

输出示例：

示例数据的输出结果如下

avg_amountavg_cost
231.723.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

题解
select 
round(sum(total_amount)/count(distinct order_id),1) avg_amount,
round(sum(price-total_amount)/count(distinct order_id),1) avg_cost
from tb_order_overall  
join 
	(
		select order_id,sum(price*cnt) price
		from tb_order_detail 
		group by order_id
	) t
 using(order_id)
where (uid,date(event_time))in
	(
		select uid,min(date(event_time)) min_day
		from tb_order_overall
		group by uid
		having date_format(min_day,"%Y-%m")="2021-10"
	)
林园857
关注
  • 2
    点赞
  • 踩
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
牛客网SQL大厂真题二刷小白白话总结（三）电商场景（某东商城）
yvonneshizhixi的博客
06-04 630
牛客网SQL大厂真题二刷小白白话总结（三）电商场景（某东商城）
牛客网SQL| 统计2021年10月每个退货率不大于0.5的商品指标
sinat_36655589的博客
08-11 60
此题目中需要注意分母为0的情况，在musql中如果分母为0，则返回null，所以可以用ifnull(expression1, expression2) ，先执行expression1，如果expression1 为null则执行expression2。
#统计2021年10月每个退货率不大于0.5的商品各项指标#
2301_79125642的博客
12-10 450
************启动代码部分本文来说码，实打实地来看看计算机到底是如何启动的，先来看看 启动的整体流程图，好有个大概认识：不要被这么一大坨吓到， 的。*************内存管理部分内存管理这部分我没有集中在一起叙述，本节只是讲述物理内存如何组织管理，页表的内核部分如何创建的，与地址转换的在启动理论那一。​ “读书能改变命运。*************启动理论部分最近整理前面所有操作系统的文章，想着做成一个集合，首先就是启动部分，要想捋清启动部分，零碎的知识点很多，本文将前面所写的杂。
牛客网SQL--某东篇
weixin_43745631的博客
01-20 1209
计算商城中2021年每月的GMV 问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。 注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。 场景逻辑说明： 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）； 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款； 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，stat
mysql牛客刷题（SQL大厂面试真题）
ljm_99的博客
05-03 2563
mysql牛客刷题（SQL大厂面试真题）系列
牛客题目网上的数据库SQL实战题目，包含详细的解题思路、代码实现和优化建议，适合准备SQL面试和实际开发的学习者
05-16
本资源包汇总了牛客题目网上精选的数据库SQL实战题目，旨在帮助学习者提升SQL技能，准备SQL相关面试，并在实际开发中应用这些技能。资源内容详尽，包括每道题目的详细解题思路、代码实现以及优化建议，非常适合SQL...
database-sql数据库基础实战
05-08
数据库SQL实战，把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试，提高自己对sql语句的理解，尽量少用第三方插件
数据库 面试题 实战解答
01-21
目录：1、触发器的作用？2、什么是存储过程？用什么来调用？...14、SQL 约束有哪几种？15、数据库的乐观锁和悲观锁是什么？16、事务隔离级别？17、Truncate与delete区别？1，使用 DELETE 语句删除数据时，Or
数据库实战把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试.zip
05-05
数据库实战把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试.zip数据库实战把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试.zip数据库实战把一些工作中遇到的以及在...
数据分析之SQL常问知识点&sql五十题示例.zip
最新发布
05-17
通过这份资源，您将能够轻松应对SQL面试中的常见问题，快速掌握SQL的高级技巧，并在实际数据分析工作中游刃有余。无论您是初学者还是想要提升技能的资深用户，这都将是一份极具价值的资源。 现在就购买这份资源，...
牛客网SQL大厂面试真题（一）
荔枝的FM
05-06 1744
SQL1 各个视频的平均完播率 SELECT log.video_id, ROUND(SUM(IF(TIMESTAMPDIFF(SECOND, log.start_time, log.end_time) >= info.duration, 1, 0)) / COUNT(1), 3) AS avg_comp_play_rate FROM tb_user_video_log AS log JOIN tb_video_info AS info ON log.video_id = info.video_i
【牛客刷题-SQL大厂面试真题】NO3.电商场景（某东商城）
IT邦德
07-31 2233
体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧
mysql牛客网大厂面试刷题深度解析篇，每日一更（持续更新）
weixin_45699541的博客
09-05 507
近一个月内的时间 此时将他建成一张表然后进行。
阿里笔试sql
liuxiaoer1的专栏
10-28 593
1.假定你当前有两张淘宝交易订单表order和sub_oder,存储于hive环境，其表结构信息如下，一个订单ID下可能多个子订单，一个子订单代表一个买家在一个卖家购买的一种商品，可能购买多件，整个支付金额是在主订单上。 create table order（ order_id bigint --订单ID ,sub_order_id bigint --子订单ID ,seller_id bigint --卖家ID ...
牛客网 SQL16 零食类商品中复购率top3高的商品
m0_46235332的博客
04-27 1097
描述 商品信息表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
【SQL】面试题（牛客网 SQL 大厂真题汇总）
weixin_44623551的博客
08-27 2885
【SQL】面试题（时间函数+窗口函数+行转列）
SQL每日练习（一）
codelady_g的博客
04-27 1074
sql练习
牛客SQL 大厂面试真题 某东商城 6套代码及解析
qq118640594X的博客
11-09 752
3.电商场景（某东商城） SQL168 计算商城中2021年每月的GMV SQL169 统计2021年10月每个退货率不大于0.5的商品各项指标 SQL170 某店铺的各商品毛利率及店铺整体毛利率 SQL171 零食类商品中复购率top3高的商品 SQL172 10月的新户客单价和获客成本 SQL173 店铺901国庆期间的7月动销率和滞销率
牛客SQL3
weixin_44681259的博客
03-15 315
sql
2023SQL面试题
03-29
很抱歉，我无法提供具体的2023年的SQL面试题，因为我无法预测未来的面试题目。但是，我可以为您提供一些常见的SQL面试题，供您参考： 1. 什么是SQL？它的作用是什么？ 2. SQL中的SELECT语句用于做什么？ 3. 什么是数据库索引？它有什么作用？ 4. SQL中的JOIN操作是用来做什么的？ 5. 什么是SQL注入？如何防止SQL注入攻击？ 6. SQL中的GROUP BY和HAVING子句有什么区别？ 7. 什么是事务？如何确保数据库操作的原子性？ 8. SQL中的UNION和UNION ALL有什么区别？ 9. 如何在SQL中进行数据的插入、更新和删除操作？ 10. 什么是视图？它有什么作用？

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值