当前位置：网站首页>【牛客刷题-SQL大厂面试真题】NO5.某宝店铺分析（电商模式）
【牛客刷题-SQL大厂面试真题】NO5.某宝店铺分析（电商模式）
2022-08-04 12:53:00 【IT邦德】
体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧~
https://www.nowcoder.com/link/pc_csdncpt_itbd_sql
SQL180 某宝店铺的SPU数量
建表语句
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES('A001', 'A', 100, 20);
INSERT INTO product_tb VALUES('A002', 'A', 120, 30);
INSERT INTO product_tb VALUES('A003', 'A', 200, 15);
INSERT INTO product_tb VALUES('B001', 'B', 130, 18);
INSERT INTO product_tb VALUES('B002', 'B', 150, 22);
INSERT INTO product_tb VALUES('B003', 'B', 125, 10);
INSERT INTO product_tb VALUES('B004', 'B', 155, 12);
INSERT INTO product_tb VALUES('C001', 'C', 260, 25);
INSERT INTO product_tb VALUES('C002', 'C', 280, 18);
需求
请你统计每款的SPU（货号）数量，并按SPU数量降序排序
答案
select style_id,count(item_id) as SPU_num
from product_tb
group by style_id
order by SPU_num desc
SQL181 某宝店铺的实际销售额与客单价
建表语句
drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150);
需求
请你统计实际总销售额与客单价（人均付费，总收入/总用户数，结果保留两位小数）
答案
select sum(sales_price) as sales_total,
round(sum(sales_price)/count(distinct user_id),2) as per_trans
from sales_tb
SQL182 某宝店铺折扣率
建表语句
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES('A001', 'A', 100, 20);
INSERT INTO product_tb VALUES('A002', 'A', 120, 30);
INSERT INTO product_tb VALUES('A003', 'A', 200, 15);
INSERT INTO product_tb VALUES('B001', 'B', 130, 18);
INSERT INTO product_tb VALUES('B002', 'B', 150, 22);
INSERT INTO product_tb VALUES('B003', 'B', 125, 10);
INSERT INTO product_tb VALUES('B004', 'B', 155, 12);
INSERT INTO product_tb VALUES('C001', 'C', 260, 25);
INSERT INTO product_tb VALUES('C002', 'C', 280, 18);
drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150);
需求
请你统计折扣率（GMV/吊牌金额，GMV指的是成交金额），折扣率保留两位小数：
' 答案
select
round(sum(sales_price)*100/sum(sales_num*tag_price),2) discount_rate
from sales_tb left join product_tb using(item_id)
SQL183 某宝店铺动销率与售罄率
建表语句
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES('A001', 'A', 100, 20);
INSERT INTO product_tb VALUES('A002', 'A', 120, 30);
INSERT INTO product_tb VALUES('A003', 'A', 200, 15);
INSERT INTO product_tb VALUES('B001', 'B', 130, 18);
INSERT INTO product_tb VALUES('B002', 'B', 150, 22);
INSERT INTO product_tb VALUES('B003', 'B', 125, 10);
INSERT INTO product_tb VALUES('B004', 'B', 155, 12);
INSERT INTO product_tb VALUES('C001', 'C', 260, 25);
INSERT INTO product_tb VALUES('C002', 'C', 280, 18);
drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150);
需求
请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序
答案
select
style_id
,round(sum(t2.sales_num)/(sum(inventory)-sum(t2.sales_num))*100,2) pin_rate
,round(sum(sales_price)/sum(tag_price*inventory)*100,2) sell_rate
from product_tb t1 join
(select
item_id
,sum(sales_num) sales_num
,sum(sales_price) sales_price
from sales_tb
group by item_id) t2
using(item_id)
group by style_id
order by style_id
SQL184 某宝店铺连续2天及以上购物的用户及其对应的天数
建表语句
drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150);
需求
请你统计连续2天及以上在该店铺购物的用户及其对应的次数（若有多个用户，按user_id升序排序）
答案
select user_id,
count(diff) date_cnt
from
(select user_id,
sales_date - rk diff
from
(select user_id,
sales_date,
rank() over(partition by sales_date) rk
from (select distinct user_id,sales_date from sales_tb)aa )a) b
group by user_id
having date_cnt>=2
边栏推荐
- 内存定位利器-ASAN使用小结
- 罗振宇的A股梦，咋这么难圆？
- Js获取当前页面url参数
- Chinese valentine's day of young people crazy to make money, earn 140000 a week
- Small program on how to play in the construction of e-government service platform value
- 正确使用Impala的invalidate metadata与refresh语句
- Matlab记录
- 干货丨数学规划视角下的分货优化解题思路
- Analysis and comparison of mobile cross-end technical solutions
- ES 节点2G内存分析
猜你喜欢
随机推荐
Cows 树状数组
Flutter教程大全合集（2022年版）
项目里的各种配置，你都了解吗？
面试官：连 INSERT INTO SET 都不知道怎么用，你这3年都干些什么了？
【解决方案 三十一】Navicat数据库结构同步
企业应当实施的5个云安全管理策略
Django使用腾讯云发送短信并存入redis
Oracle 19c 单实例 19.3.0 升级到19.11.0 详细教程
两年独立开发经验程序员告诉我们赚钱的经验（听听真正赚到钱的高手做法）
如何治理资源浪费？百度云原生成本优化最佳实践
Js获取当前页面url参数
rpm安装提示error: XXX: not an rpm package (or package manifest):
Motion Rule (16)-Union Check Basic Questions-Grid Game
Chinese valentine's day of young people crazy to make money, earn 140000 a week
Escape character is ‘^]’什么意思?怎么使用telnet
跨链桥已成行业最大安全隐患 为什么和怎么办
永磁同步电机FOC驱动代码讲解
LeetCode Daily Question (858. Mirror Reflection)
SCA兼容性分析工具（ORACLE/MySQL/DB2---＆gt;MogDB/openGauss/PostgreSQL）
5 cloud security management strategies enterprises should implement