【牛客刷题-SQL大厂面试真题】NO3.电商场景（某东商城）
体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧~
https://www.nowcoder.com/link/pc_csdncpt_itbd_sql
文章目录
- 前言
前言
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
🐴 SQL168 计算商城中2021年每月的GMV
🚀 建表语句
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);📖 需求
场景逻辑说明：用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。🍌🍌 答案
select date_format(event_time,"%Y-%m") month,sum(total_amount) GMVfrom tb_order_overallwhere status in (1,0) and year(event_time) = 2021group by monthhaving GMV > 100000order by GMV
🐴 SQL169 统计2021年10月每个退货率不大于0.5的商品各项指标
🚀 建表语句
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);📖 需求
问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，注：商品点展比=点击数÷展示数；加购率=加购数÷点击数；成单率=付款数÷加购数；退货率=退款数÷付款数，当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。🍌🍌 答案
selectproduct_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_eventWHEREleft(date(event_time),7)='2021-10'group by product_idorder by product_id
🐴 SQL170 某店铺的各商品毛利率及店铺整体毛利率
🚀 建表语句
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);📖 需求
场景逻辑说明：用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，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_rateFROM ( 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 = 901 and DATE(event_time) >= "2021-10-01" ) as t_product_in_each_order GROUP BY product_id WITH ROLLUP HAVING profit_rate > 24.9 OR product_id IS NULL ORDER BY product_id) as t1;
🐴 SQL171 零食类商品中复购率top3高的商品
🚀 建表语句
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);📖 需求
场景逻辑说明：用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， 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 left join tb_order_overall as b on a.order_id=b.order_id left join tb_product_info as c on c.product_id=a.product_id where datediff((select max(event_time) from tb_order_overall),event_time)<90 and tag='零食' group by a.product_id,b.uid ) as dgroup by product_idorder by repurchase_rate desc,product_id limit 3
🐴 SQL172 10月的新户客单价和获客成本
🚀 建表语句
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);📖 需求
问题：请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。🍌🍌 答案
select round(avg(total_amount),1) avg_amount,round(avg(sum_price-total_amount),1) avg_costfrom( select uid,event_time ,total_amount ,row_number() over(partition by uid order by event_time) rk ,sum(price*cnt) over(partition by order_id) sum_price ,status from tb_order_detail tod join tb_order_overall too using(order_id) where status = 1 ) twhere rk = 1and date_format(event_time,'%Y-%m')='2021-10'
🐴 SQL173 店铺901国庆期间的7日动销率和滞销率
🚀 建表语句
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 (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);INSERT INTO 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(distinct if(timestampdiff(day,dt,dt1) between 0 and 6, tb1.product_id,null))/count(distinct if(dt1>=date(release_time),tb3.product_id,null)),3) sale_rate,1-round(count(distinct if(timestampdiff(day,dt,dt1) between 0 and 6, tb1.product_id,null))/count(distinct if(dt1>=date(release_time),tb3.product_id,null)),3) unsale_ratefrom (select date(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 left join tb_order_detail b on a.order_id=b.order_id left join tb_product_info c on b.product_id=c.product_idwhere shop_id=901) tb1 left join tb_product_info tb3 on tb1.product_id=tb3.product_id where shop_id=901group by dt1 
