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

体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧~

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

文章目录

前言

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

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

🚀 建表语句

DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    uid INTNOTNULLCOMMENT'用户ID',
    event_time datetimeCOMMENT'下单时间',
    total_amount DECIMALNOTNULLCOMMENT'订单总金额',
    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);

📖 需求

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

🍌🍌 答案

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

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

🚀 建表语句

DROPTABLEIFEXISTS tb_user_event;CREATETABLE tb_user_event (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid INTNOTNULLCOMMENT'用户ID',
    product_id INTNOTNULLCOMMENT'商品ID',
    event_time datetimeCOMMENT'行为时间',
    if_click TINYINTCOMMENT'是否点击',
    if_cart TINYINTCOMMENT'是否加购物车',
    if_payment TINYINTCOMMENT'是否付款',
    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);

📖 需求

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

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

🍌🍌 答案

select
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
WHEREleft(date(event_time),7)='2021-10'groupby product_id
orderby product_id

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

🚀 建表语句

DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    uid INTNOTNULLCOMMENT'用户ID',
    event_time datetimeCOMMENT'下单时间',
    total_amount DECIMALNOTNULLCOMMENT'订单总金额',
    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 (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    product_id INTNOTNULLCOMMENT'商品ID',
    shop_id INTNOTNULLCOMMENT'店铺ID',
    tag VARCHAR(12)COMMENT'商品类别标签',
    in_price DECIMALNOTNULLCOMMENT'进货价格',
    quantity INTNOTNULLCOMMENT'进货数量',
    release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    product_id INTNOTNULLCOMMENT'商品ID',
    price DECIMALNOTNULLCOMMENT'商品单价',
    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);

📖 需求

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

🍌🍌 答案

SELECT product_id, CONCAT(profit_rate,"%")as profit_rate
FROM(SELECT IFNULL(product_id,'店铺汇总')as product_id,ROUND(100*(1-SUM(in_price*cnt)/SUM(price*cnt)),1)as profit_rate
    FROM(SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        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
    GROUPBY product_id
    WITH ROLLUPHAVING profit_rate >24.9OR product_id ISNULLORDERBY product_id
)as t1;

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

🚀 建表语句

DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    uid INTNOTNULLCOMMENT'用户ID',
    event_time datetimeCOMMENT'下单时间',
    total_amount DECIMALNOTNULLCOMMENT'订单总金额',
    total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    product_id INTNOTNULLCOMMENT'商品ID',
    shop_id INTNOTNULLCOMMENT'店铺ID',
    tag VARCHAR(12)COMMENT'商品类别标签',
    in_price DECIMALNOTNULLCOMMENT'进货价格',
    quantity INTNOTNULLCOMMENT'进货数量',
    release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    product_id INTNOTNULLCOMMENT'商品ID',
    price DECIMALNOTNULLCOMMENT'商品单价',
    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);

📖 需求

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

🍌🍌 答案

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

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

🚀 建表语句

DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    uid INTNOTNULLCOMMENT'用户ID',
    event_time datetimeCOMMENT'下单时间',
    total_amount DECIMALNOTNULLCOMMENT'订单总金额',
    total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    product_id INTNOTNULLCOMMENT'商品ID',
    shop_id INTNOTNULLCOMMENT'店铺ID',
    tag VARCHAR(12)COMMENT'商品类别标签',
    in_price DECIMALNOTNULLCOMMENT'进货价格',
    quantity INTNOTNULLCOMMENT'进货数量',
    release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    product_id INTNOTNULLCOMMENT'商品ID',
    price DECIMALNOTNULLCOMMENT'商品单价',
    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);

📖 需求

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

🍌🍌 答案

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

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

🚀 建表语句

DROPTABLEIFEXISTS tb_order_overall;CREATETABLE tb_order_overall (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    uid INTNOTNULLCOMMENT'用户ID',
    event_time datetimeCOMMENT'下单时间',
    total_amount DECIMALNOTNULLCOMMENT'订单总金额',
    total_cnt INTNOTNULLCOMMENT'订单商品总件数',`status`TINYINTNOTNULLCOMMENT'订单状态')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_product_info;CREATETABLE tb_product_info (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    product_id INTNOTNULLCOMMENT'商品ID',
    shop_id INTNOTNULLCOMMENT'店铺ID',
    tag VARCHAR(12)COMMENT'商品类别标签',
    in_price DECIMALNOTNULLCOMMENT'进货价格',
    quantity INTNOTNULLCOMMENT'进货数量',
    release_time datetimeCOMMENT'上架时间')CHARACTERSET utf8 COLLATE utf8_bin;DROPTABLEIFEXISTS tb_order_detail;CREATETABLE tb_order_detail (
    id INTPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    order_id INTNOTNULLCOMMENT'订单号',
    product_id INTNOTNULLCOMMENT'商品ID',
    price DECIMALNOTNULLCOMMENT'商品单价',
    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);

📖 需求

问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

注：
动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

🍌🍌 答案

select dt1,round(count(distinctif(timestampdiff(day,dt,dt1)between0and6, 
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
from(selectdate(event_time) dt1 from tb_order_overall having dt1 between'2021-10-01'and'2021-10-03') tb2 
,(select b.product_id,date(event_time) dt from 
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
where shop_id=901) tb1 
leftjoin tb_product_info tb3 
on tb1.product_id=tb3.product_id 
where shop_id=901groupby dt1