牛客网SQL真题 电商系列
题目来源：牛客网
13 计算商城中2021年每月的GMV
描述：
现有订单总表tb_order_overall
| id | order_id | uid | event_time | total_amount | total_cnt | status |
|---|---|---|---|---|---|---|
| 1 | 301001 | 101 | 2021-10-01 10:00:00 | 15900 | 2 | 1 |
| 2 | 301002 | 101 | 2021-10-01 11:00:00 | 15900 | 2 | 1 |
| 3 | 301003 | 102 | 2021-10-02 10:00:00 | 34500 | 8 | 0 |
| 4 | 301004 | 103 | 2021-10-12 10:00:00 | 43500 | 9 | 1 |
| 5 | 301005 | 105 | 2021-11-01 10:00:00 | 31900 | 7 | 1 |
| 6 | 301006 | 102 | 2021-11-02 10:00:00 | 24500 | 6 | 1 |
| 7 | 301007 | 102 | 2021-11-03 10:00:00 | -24500 | 6 | 2 |
| 8 | 301008 | 104 | 2021-11-04 10:00:00 | 55500 | 12 | 0 |
（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）
场景逻辑说明：
- 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；
- 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；
- 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。
问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。
注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。
输出示例：
示例数据输出如下：
| month | GMV |
|---|---|
| 2021-10 | 109800 |
| 2021-11 | 111900 |
解释：
2021年10月有3笔已付款的订单，1笔未付款订单，总交易金额为109800；2021年11月有2笔已付款订单，1笔未付款订单，
总交易金额为111900（还有1笔退款订单由于已计算了付款的订单金额，无需计算在GMV中）。
分析思路
- 时间格式转换：date_format(event_time, '%Y-%m')
- 订单状态：不考虑退款订单，只需要考虑status为0或1的情况
- 年份限制、GMV限制、排序
select date_format(event_time, '%Y-%m') month, round(sum(total_amount),0) GMV
from tb_order_overall
where status in (0, 1)
and year(event_time) = 2021
group by month
having GMV > 100000
order by GMV;
14 统计2021年10月每个退货率不大于0.5的商品各项指标
现有用户对展示的商品行为表tb_user_event
| id | uid | product_id | event_time | if_click | if_cart | if_payment | if_refund |
|---|---|---|---|---|---|---|---|
| 1 | 101 | 8001 | 2021-10-01 10:00:00 | 0 | 0 | 0 | 0 |
| 2 | 102 | 8001 | 2021-10-01 10:00:00 | 1 | 0 | 0 | 0 |
| 3 | 103 | 8001 | 2021-10-01 10:00:00 | 1 | 1 | 0 | 0 |
| 4 | 104 | 8001 | 2021-10-02 10:00:00 | 1 | 1 | 1 | 0 |
| 5 | 105 | 8001 | 2021-10-02 10:00:00 | 1 | 1 | 1 | 0 |
| 6 | 101 | 8002 | 2021-10-03 10:00:00 | 1 | 1 | 1 | 0 |
| 7 | 109 | 8001 | 2021-10-04 10:00:00 | 1 | 1 | 1 | 1 |
（uid-用户ID, product_id-商品ID, event_time-行为时间, if_click-是否点击, if_cart-是否加购物车, if_payment-是否付款, if_refund-是否退货退款）
问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，
注：
- 商品点展比=点击数÷展示数；
- 加购率=加购数÷点击数；
- 成单率=付款数÷加购数；退货率=退款数÷付款数，
- 当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。
输出示例：
示例数据的输出结果如下
| product_id | ctr | cart_rate | payment_rate | refund_rate |
|---|---|---|---|---|
| 8001 | 0.833 | 0.800 | 0.750 | 0.333 |
| 8002 | 1.000 | 1.000 | 1.000 | 0.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位小数）；
分析思路
-
点展比、加购率、成单率、退货率
分别以展示次数、点击次数、加购次数、付款次数作为分母，求得各项比率。
考虑分母为0的情况，使用
if函数处理。使用
round函数处理保留小数位数 -
退货率<=0.5、时间范围、商品排序
select
product_id,
round(sum(if_click) / count(product_id), 3) ctr,
round(if(sum(if_click)=0, 0, sum(if_cart)/sum(if_click)), 3) cart_rate,
round(if(sum(if_cart)=0, 0, sum(if_payment)/sum(if_cart)), 3) payment_rate,
round(if(sum(if_refund)=0, 0, sum(if_refund)/sum(if_payment)), 3) refund_rate
from tb_user_event
where date_format(event_time, '%Y-%m') = '2021-10'
group by product_id
having refund_rate <= 0.5
order by product_id;
15 某店铺的各商品毛利率及店铺整体毛利率
商品信息表tb_product_info
| id | product_id | shop_id | tag | in_price | quantity | release_time |
|---|---|---|---|---|---|---|
| 1 | 8001 | 901 | 家电 | 6000 | 100 | 2020-01-01 10:00:00 |
| 2 | 8002 | 902 | 家电 | 12000 | 50 | 2020-01-01 10:00:00 |
| 3 | 8003 | 901 | 3C数码 | 12000 | 50 | 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 | 301001 | 101 | 2021-10-01 10:00:00 | 30000 | 3 | 1 |
| 2 | 301002 | 102 | 2021-10-01 11:00:00 | 23900 | 2 | 1 |
| 3 | 301003 | 103 | 2021-10-02 10:00:00 | 31000 | 2 | 1 |
（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）
订单明细表tb_order_detail
| id | order_id | product_id | price | cnt |
|---|---|---|---|---|
| 1 | 301001 | 8001 | 8500 | 2 |
| 2 | 301001 | 8002 | 15000 | 1 |
| 3 | 301002 | 8001 | 8500 | 1 |
| 4 | 301002 | 8002 | 16000 | 1 |
| 5 | 301003 | 8002 | 14000 | 1 |
| 6 | 301003 | 8003 | 18000 | 1 |
（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_id | profit_rate |
|---|---|
| 店铺汇总 | 31.0% |
| 8001 | 29.4% |
| 8003 | 33.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%
分析思路
-
店铺汇总
使用sum函数获取总进价成本和总销售收入，round函数保留1位小数，concat添加百分号；
注意订单的时间范围是2021年10月以来，通过where语句和子查询进行选择；（status=1）
按照店铺id进行分组。
-
各商品毛利率
由于需要对商品的毛利率进行筛选，所以先经过having语句筛选，再添加百分号
-
组合查询 UNION
使用union将两组查询组合。
select
'店铺汇总' as product_id,
concat(round((1 - sum(in_price * cnt)/sum(price * cnt))*100, 1), '%') profit_rate
from tb_product_info tb1
join tb_order_detail tb2
on tb1.product_id = tb2.product_id
where tb1.shop_id = 901
and tb2.order_id in (
select order_id from tb_order_overall
where date_format(event_time, '%Y-%m') >= '2021-10')
group by tb1.shop_id
union all
select product_id, concat(profit, '%') profit_rate
from (
select
tb1.product_id,
round((1 - sum(in_price * cnt)/sum(price * cnt))*100, 1) profit
from tb_product_info tb1
join tb_order_detail tb2
on tb1.product_id = tb2.product_id
where tb1.shop_id = 901
and tb2.order_id in (
select order_id from tb_order_overall
where date_format(event_time, '%Y-%m') >= '2021-10')
group by tb1.product_id
having profit > 24.9
order by profit
) t0;
思考：如果用left join是不是可以ifnull？
16 零食类商品中复购率top3高的商品
描述
商品信息表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 |
（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 | 301001 | 101 | 2021-09-30 10:00:00 | 140 | 1 | 1 |
| 2 | 301002 | 102 | 2021-10-01 11:00:00 | 235 | 2 | 1 |
| 3 | 301011 | 102 | 2021-10-31 11:00:00 | 250 | 2 | 1 |
| 4 | 301003 | 101 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 5 | 301013 | 105 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 6 | 301005 | 104 | 2021-10-03 10:00:00 | 170 | 1 | 1 |
（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）
订单明细表tb_order_detail
| id | order_id | product_id | price | cnt |
|---|---|---|---|---|
| 1 | 301001 | 8002 | 150 | 1 |
| 2 | 301011 | 8003 | 200 | 1 |
| 3 | 301011 | 8001 | 80 | 1 |
| 4 | 301002 | 8001 | 85 | 1 |
| 5 | 301002 | 8003 | 180 | 1 |
| 6 | 301003 | 8002 | 140 | 1 |
| 7 | 301003 | 8003 | 180 | 1 |
| 8 | 301013 | 8002 | 140 | 2 |
| 9 | 301005 | 8003 | 180 | 1 |
（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）
场景逻辑说明：
- 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态-****订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
- 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-****订单状态为1表示已付款；
- 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。
问题：请统计零食类商品中复购率top3高的商品。
注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序
输出示例：
示例数据的输出结果如下：
| product_id | repurchase_rate |
|---|---|
| 8001 | 1.000 |
| 8002 | 0.500 |
| 8003 | 0.333 |
解释：
商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；
商品8002被101购买了两次，被105购买了1次，复购率0.500；
商品8003被102购买两次，被101和105各购买1次，复购率为0.333。
分析思路
-
复购率
三表连接，按照商品被某用户购买的次数进行0-1划分（便于使用均值获取复购率），用户购买该商品次数大于1记为1，表示复购，否则记为0，表示没有复购；
-
时间处理
近90天内的最大日期记为当天，那么使用max函数，最大日期与下单时间的差值要小于90
-
分组情况
子查询按照商品id和用户id分组。
获取复购率时按照商品id分组，还需排序，限制top3。
select product_id, round(avg(repurchase), 3) repurchase_rate
from (
select tb2.product_id, uid, if(count(event_time)>1, 1, 0) repurchase
from tb_order_overall tb1
join tb_order_detail tb2 on tb1.order_id = tb2.order_id
join tb_product_info tb3 on tb2.product_id = tb3.product_id
where tag = '零食'
and datediff((select max(event_time) from tb_order_overall), event_time) < 90
group by product_id, uid) t0
group by product_id
order by repurchase_rate desc, product_id
limit 3;
17 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
分析思路
-
新用户
2021年10月的新用户：有记录以来，最早（min）下单时间在2021年10月的用户
-
首单客单价
结合1的最早下单时间event_time，对应的订单就是首单，首单客单价就是total_amount
-
获客成本
获客成本 = 订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的
select
round(avg(total_amount), 1) as avg_amount,
round(avg(product_value - total_amount), 1) as avg_cost
from tb_order_overall tb1
join (
select order_id, sum(price*cnt) product_value from tb_order_detail group by order_id) tb2
on tb1.order_id = tb2.order_id
where event_time in (
select min(event_time) from tb_order_overall GROUP BY uid)
and date_format(event_time, '%Y-%m') = '2021-10';
18 店铺901国庆期间的7日动销率和滞销率
描述
商品信息表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 |
（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 | 301004 | 102 | 2021-09-30 10:00:00 | 170 | 1 | 1 |
| 2 | 301005 | 104 | 2021-10-01 10:00:00 | 160 | 1 | 1 |
| 3 | 301003 | 101 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 4 | 301002 | 102 | 2021-10-03 11:00:00 | 235 | 2 | 1 |
（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）
订单明细表tb_order_detail
| id | order_id | product_id | price | cnt |
|---|---|---|---|---|
| 1 | 301004 | 8002 | 180 | 1 |
| 2 | 301005 | 8002 | 170 | 1 |
| 3 | 301002 | 8001 | 85 | 1 |
| 4 | 301002 | 8003 | 180 | 1 |
| 5 | 301003 | 8002 | 150 | 1 |
| 6 | 301003 | 8003 | 180 | 1 |
（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）
问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。
注：
- 动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
- 滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
- 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。
输出示例：
示例数据的输出结果如下：
| dt | sale_rate | unsale_rate |
|---|---|---|
| 2021-10-01 | 0.333 | 0.667 |
| 2021-10-02 | 0.667 | 0.333 |
| 2021-10-03 | 1.000 | 0.000 |
解释：
10月1日的近7日（9月25日---10月1日）店铺901有销量的商品有8002，截止当天在售商品数为3，动销率为0.333，滞销率为0.667；
10月2日的近7日（9月26日---10月2日）店铺901有销量的商品有8002、8003，截止当天在售商品数为3，动销率为0.667，滞销率为0.333；
10月3日的近7日（9月27日---10月3日）店铺901有销量的商品有8002、8003、8001，截止当天店铺901在售商品数为3，动销率为1.000，
滞销率为0.000；
分析思路
- 选择所需国庆头三天的三个日期
- 每日售出商品的product_id
- 每日在售商品数
- 近七日的动销率和滞销率
with t1 as (
select DISTINCT date(event_time) dt
from tb_order_overall
where date(event_time) BETWEEN '2021-10-01' and '2021-10-03'
and status = 1),
t2 as(
select date(event_time) dt, tb1.product_id
from tb_order_detail tb1
join tb_order_overall tb2 on tb1.order_id = tb2.order_id
join tb_product_info tb3 on tb1.product_id = tb3.product_id and shop_id='901'),
t3 as (
select date(event_time) dt,
count(distinct if(datediff(date(event_time), date(release_time)) >= 0, product_id, null)) onsale
from tb_product_info, tb_order_overall
where shop_id = '901'
group by dt)
select t1.dt,
round(count(distinct product_id) / onsale, 3) sale_rate,
round(1 - count(distinct product_id) / onsale, 3) unsale_rate
from t1 left join t2 on datediff(t1.dt, t2.dt) BETWEEN 0 and 6
join t3 on t1.dt = t3.dt
group by t1.dt
order by t1.dt;
