0
0

【牛客刷题-SQL大厂面试真题】NO3.电商场景（某东商城）

在这里插入图片描述
体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧~

https://www.nowcoder.com/link/pc_csdncpt_itbd_sql

文章目录

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

在这里插入图片描述

🐴 SQL168 计算商城中2021年每月的GMV

🚀 建表语句

  1. DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
  2.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  3.     order_id INTNOTNULLCOMMENT'订单号',
  4.     uid INTNOTNULLCOMMENT'用户ID',
  5.     event_time datetimeCOMMENT'下单时间',
  6.     total_amount DECIMALNOTNULLCOMMENT'订单总金额',
  7.     total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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);

📖 需求

  1. 场景逻辑说明：
  2. 用户将购物车中多件商品一起下单时，订单总表会生成一个订单
  3. （但此时未付款，status-订单状态为0，表示待付款）；
  4. 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；
  5. 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，
  6. 订单号为退款单号，status-订单状态为2表示已退款）。
  7. 问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。
  8. 注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。

🍌🍌 答案

  1. select date_format(event_time,"%Y-%m")month,sum(total_amount) GMV
  2. from tb_order_overall
  3. wherestatusin(1,0)andyear(event_time)=2021groupbymonthhaving GMV >100000orderby GMV

在这里插入图片描述

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

🚀 建表语句

  1. DROPTABLEIFEXISTS tb_user_event;CREATETABLE tb_user_event (
  2.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  3.     uid INTNOTNULLCOMMENT'用户ID',
  4.     product_id INTNOTNULLCOMMENT'商品ID',
  5.     event_time datetimeCOMMENT'行为时间',
  6.     if_click TINYINTCOMMENT'是否点击',
  7.     if_cart TINYINTCOMMENT'是否加购物车',
  8.     if_payment TINYINTCOMMENT'是否付款',
  9.     if_refund TINYINTCOMMENT'是否退货退款')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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);

📖 需求

  1. 问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，
  3. 注：
  4. 商品点展比=点击数÷展示数；
  5. 加购率=加购数÷点击数；
  6. 成单率=付款数÷加购数；退货率=退款数÷付款数，
  7. 当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

🍌🍌 答案

  1. select
  2. product_id,round(sum(if_click)/count(id),3),round(sum(if_cart)/sum(if_click),3),round(sum(if_payment)/sum(if_cart),3),round(sum(if_refund)/sum(if_payment),3)from tb_user_event
  3. WHEREleft(date(event_time),7)='2021-10'groupby product_id
  4. orderby product_id

在这里插入图片描述

🐴 SQL170 某店铺的各商品毛利率及店铺整体毛利率

🚀 建表语句

  1. DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
  2.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  3.     order_id INTNOTNULLCOMMENT'订单号',
  4.     uid INTNOTNULLCOMMENT'用户ID',
  5.     event_time datetimeCOMMENT'下单时间',
  6.     total_amount DECIMALNOTNULLCOMMENT'订单总金额',
  7.     total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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);DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
  8.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  9.     product_id INTNOTNULLCOMMENT'商品ID',
  10.     shop_id INTNOTNULLCOMMENT'店铺ID',
  11.     tag VARCHAR(12)COMMENT'商品类别标签',
  12.     in_price DECIMALNOTNULLCOMMENT'进货价格',
  13.     quantity INTNOTNULLCOMMENT'进货数量',
  14.     release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
  15.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  16.     order_id INTNOTNULLCOMMENT'订单号',
  17.     product_id INTNOTNULLCOMMENT'商品ID',
  18.     price DECIMALNOTNULLCOMMENT'商品单价',
  19.     cnt INTNOTNULLCOMMENT'下单数量')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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');INSERTINTO 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);

📖 需求

  1. 场景逻辑说明：
  2. 用户将购物车中多件商品一起下单时，
  3. 订单总表会生成一个订单（但此时未付款，status-订单状态为0表示待付款），
  4. 在订单明细表生成该订单中每个商品的信息；
  5. 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1表示已付款；
  6. 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，
  7. 订单号为退款单号，status-订单状态为2表示已退款）。
  8. 问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
  9. 注：商品毛利率=(1-进价/平均单件售价)*100%；
  10. 店铺毛利率=(1-总进价成本/总销售收入)*100%。
  11. 结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

🍌🍌 答案

  1. SELECT product_id, CONCAT(profit_rate,"%")as profit_rate
  2. FROM(SELECT IFNULL(product_id,'店铺汇总')as product_id,ROUND(100*(1-SUM(in_price*cnt)/SUM(price*cnt)),1)as profit_rate
  3.     FROM(SELECT product_id, price, cnt, in_price
  4.         FROM tb_order_detail
  5.         JOIN tb_product_info USING(product_id)JOIN tb_order_overall USING(order_id)WHERE shop_id =901andDATE(event_time)>="2021-10-01")as t_product_in_each_order
  6.     GROUPBY product_id
  7.     WITH ROLLUPHAVING profit_rate >24.9OR product_id ISNULLORDERBY product_id
  8. )as t1;

在这里插入图片描述

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

🚀 建表语句

  1. DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
  2.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  3.     order_id INTNOTNULLCOMMENT'订单号',
  4.     uid INTNOTNULLCOMMENT'用户ID',
  5.     event_time datetimeCOMMENT'下单时间',
  6.     total_amount DECIMALNOTNULLCOMMENT'订单总金额',
  7.     total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
  8.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  9.     product_id INTNOTNULLCOMMENT'商品ID',
  10.     shop_id INTNOTNULLCOMMENT'店铺ID',
  11.     tag VARCHAR(12)COMMENT'商品类别标签',
  12.     in_price DECIMALNOTNULLCOMMENT'进货价格',
  13.     quantity INTNOTNULLCOMMENT'进货数量',
  14.     release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
  15.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  16.     order_id INTNOTNULLCOMMENT'订单号',
  17.     product_id INTNOTNULLCOMMENT'商品ID',
  18.     price DECIMALNOTNULLCOMMENT'商品单价',
  19.     cnt INTNOTNULLCOMMENT'下单数量')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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');INSERTINTO 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);INSERTINTO 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);

📖 需求

  1. 场景逻辑说明：
  2. 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，
  3.  status-订单状态-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
  4. 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-订单状态为1表示已付款；
  5. 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，
  6. 订单状态为2表示已退款）。
  7. 问题：请统计零食类商品中复购率top3高的商品。
  8. 注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，
  9. 则反映出消费者对品牌的忠诚度就越高，也叫回头率
  10. 此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
  11. 近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，
  12. 并按复购率倒序、商品ID升序排序

🍌🍌 答案

  1. select product_id,round(sum(if(cnt>=2,1,0))/count(*),3) repurchase_rate from(select a.product_id,b.uid,count(uid) cnt 
  2.     from tb_order_detail as a leftjoin tb_order_overall as b on a.order_id=b.order_id
  3.     leftjoin tb_product_info as c on c.product_id=a.product_id
  4.     where datediff((selectmax(event_time)from tb_order_overall),event_time)<90and tag='零食'groupby a.product_id,b.uid
  5.     )as d
  6. groupby product_id
  7. orderby repurchase_rate desc,product_id limit3

在**加粗样式**这里插入图片描述

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

🚀 建表语句

  1. DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
  2.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  3.     order_id INTNOTNULLCOMMENT'订单号',
  4.     uid INTNOTNULLCOMMENT'用户ID',
  5.     event_time datetimeCOMMENT'下单时间',
  6.     total_amount DECIMALNOTNULLCOMMENT'订单总金额',
  7.     total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
  8.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  9.     product_id INTNOTNULLCOMMENT'商品ID',
  10.     shop_id INTNOTNULLCOMMENT'店铺ID',
  11.     tag VARCHAR(12)COMMENT'商品类别标签',
  12.     in_price DECIMALNOTNULLCOMMENT'进货价格',
  13.     quantity INTNOTNULLCOMMENT'进货数量',
  14.     release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
  15.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  16.     order_id INTNOTNULLCOMMENT'订单号',
  17.     product_id INTNOTNULLCOMMENT'商品ID',
  18.     price DECIMALNOTNULLCOMMENT'商品单价',
  19.     cnt INTNOTNULLCOMMENT'下单数量')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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');INSERTINTO 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);INSERTINTO 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);

📖 需求

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

🍌🍌 答案

  1. selectround(avg(total_amount),1) avg_amount,round(avg(sum_price-total_amount),1) avg_cost
  2. from(select 
  3.         uid,event_time
  4.         ,total_amount
  5.         ,row_number()over(partitionby uid orderby event_time) rk
  6.         ,sum(price*cnt)over(partitionby order_id) sum_price
  7.         ,statusfrom tb_order_detail tod
  8.         join tb_order_overall too
  9.         using(order_id)wherestatus=1) t
  10. where rk =1and date_format(event_time,'%Y-%m')='2021-10'

在这里插入图片描述

🐴 SQL173 店铺901国庆期间的7日动销率和滞销率

🚀 建表语句

  1. DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
  2.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  3.     order_id INTNOTNULLCOMMENT'订单号',
  4.     uid INTNOTNULLCOMMENT'用户ID',
  5.     event_time datetimeCOMMENT'下单时间',
  6.     total_amount DECIMALNOTNULLCOMMENT'订单总金额',
  7.     total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
  8.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  9.     product_id INTNOTNULLCOMMENT'商品ID',
  10.     shop_id INTNOTNULLCOMMENT'店铺ID',
  11.     tag VARCHAR(12)COMMENT'商品类别标签',
  12.     in_price DECIMALNOTNULLCOMMENT'进货价格',
  13.     quantity INTNOTNULLCOMMENT'进货数量',
  14.     release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
  15.     id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
  16.     order_id INTNOTNULLCOMMENT'订单号',
  17.     product_id INTNOTNULLCOMMENT'商品ID',
  18.     price DECIMALNOTNULLCOMMENT'商品单价',
  19.     cnt INTNOTNULLCOMMENT'下单数量')CHARACTERSET utf8 COLLATE utf8_bin;INSERTINTO 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');INSERTINTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt,`status`)VALUES(301004,102,'2021-09-30 10:00:00',170,1,1),(301005,104,'2021-10-01 10:00:00',160,1,1),(301003,101,'2021-10-02 10:00:00',300,2,1),(301002,102,'2021-10-03 11:00:00',235,2,1);INSERTINTO tb_order_detail(order_id, product_id, price, cnt)VALUES(301004,8002,180,1),(301005,8002,170,1),(301002,8001,85,1),(301002,8003,180,1),(301003,8002,150,1),(301003,8003,180,1);

📖 需求

  1. 问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。
  3. 注：
  4. 动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
  5. 滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
  6. 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

🍌🍌 答案

  1. select dt1,round(count(distinctif(timestampdiff(day,dt,dt1)between0and6, 
  2. tb1.product_id,null))/count(distinctif(dt1>=date(release_time),tb3.product_id,null)),3) sale_rate,1-round(count(distinctif(timestampdiff(day,dt,dt1)between0and6, tb1.product_id,null))/count(distinctif(dt1>=date(release_time),tb3.product_id,null)),3) unsale_rate
  3. from(selectdate(event_time) dt1 from tb_order_overall having dt1 between'2021-10-01'and'2021-10-03') tb2 
  4. ,(select b.product_id,date(event_time) dt from 
  5. tb_order_overall a leftjoin tb_order_detail b on a.order_id=b.order_id leftjoin tb_product_info c on b.product_id=c.product_id
  6. where shop_id=901) tb1 
  7. leftjoin tb_product_info tb3 
  8. on tb1.product_id=tb3.product_id 
  9. where shop_id=901groupby dt1

在这里插入图片描述
在这里插入图片描述

标签： 面试 sql java
本文转载自: https://blog.csdn.net/weixin_41645135/article/details/126081071
版权归原作者 IT邦德 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【牛客刷题-SQL大厂面试真题】NO3.电商场景（某东商城）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？