学习笔记：SQL大厂面试题集

最新推荐文章于 2024-07-26 15:30:26 发布
最新推荐文章于 2024-07-26 15:30:26 发布
阅读量1.7k 收藏 26
点赞数 1
文章标签： sql 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_61136860/article/details/120356143
版权

题源代码来自：​​​​​​数据分析-全网最全SQL面试真题(做完，面试再也不虚了-划分了知名互联网大厂、不同行业背景（如电商、游戏）、常考知识点三大部分的SQL真题）_哔哩哔哩_bilibili欢迎大家一起交流学习，课件资料会在数据蛙SQL面试题交流群持续更新~加群方式-加V：lkx941013，备注:SQL视频持续更新inghttps://www.bilibili.com/video/BV1Rh411B7sN?share_source=copy_web【724补】过好久了，再重新整理复习一下| ᴥ•́ )✧

目录

一、货拉拉面试题-用户取消率

二、小红书面试题-用户行为分析

三、 滴滴面试题(数据太多待补)

四、网易面试题-用户复购率(数据待补)

五、支付宝面试题-求支付金额在前20%的用户(数据待补)

六、中金财富证券互联网金融部面试题(数据待补)

七、游戏类面试题

八、电商类面试题

一、货拉拉面试题-用户取消率

先建表导入数据

CREATE TABLE IF NOT EXISTS trips (
id VARCHAR(255),
client_id VARCHAR(255),
driver_id VARCHAR(255),
city_id VARCHAR(255),
STATUS VARCHAR(255),
request_at VARCHAR(255));
INSERT INTO trips VALUES('1','1','10','1','completed','2019-10-01');
INSERT INTO trips VALUES('2','2','11','1','cancelled_by_driver','2019-10-01');
INSERT INTO trips VALUES('3','3','12','6','completed','2019-10-01');
INSERT INTO trips VALUES('4','4','13','6','cancelled_by_driver','2019-10-01');
INSERT INTO trips VALUES('5','1','10','1','completed','2019-10-02');
INSERT INTO trips VALUES('6','2','11','6','completed','2019-10-02');
INSERT INTO trips VALUES('7','3','12','6','completed','2019-10-02');
INSERT INTO trips VALUES('8','2','12','12','completed','2019-10-03');
INSERT INTO trips VALUES('9','3','10','12','completed','2019-10-03');
INSERT INTO trips VALUES('10','4','13','12','cancelled_by_driver','2019-10-03');


CREATE TABLE IF NOT EXISTS users (
users_id VARCHAR(255),
banned VARCHAR(255),
ROLE VARCHAR(255));
INSERT INTO  users VALUES('1','no','client');
INSERT INTO  users VALUES('2','yes','client');
INSERT INTO  users VALUES('3','no','client');
INSERT INTO  users VALUES('4','no','client');
INSERT INTO  users VALUES('10','no','driver');
INSERT INTO  users VALUES('11','no','driver');
INSERT INTO  users VALUES('12','no','driver');
INSERT INTO  users VALUES('13','no','driver');

SELECT * FROM trips;
SELECT * FROM users;

 求2019-10-01到03期间，非被禁止用户取消率,保留两位小数。

 用户取消率=用户取消数/用户数=SUM(IF(STATUS='completed',0,1))/COUNT(*)由于取消情况较复杂，有乘客取消和司机取消，所以采用反集的概念，若判断订单完成，输出0，否则为1.

SELECT request_at,COUNT(*),SUM(IF (STATUS='completed', 0 , 1)),ROUND(SUM(IF (STATUS='completed', 0 , 1))/COUNT(*),2)a
FROM trips RIGHT JOIN users ON trips.client_id=users.users_id
WHERE banned='no' AND request_at BETWEEN '2019-10-01' AND'2019-10-03'
GROUP BY request_at;

二、小红书面试题-用户行为分析

有订单事务表、收藏事务表，要求：请用一句sql取出所有用户对商品的行为特征，
特征分为已购买、购买未收藏、收藏未购买、收藏且购买

CREATE TABLE redbk_orders(
id INT,
user_id VARCHAR(10),
item_id INT,
par_time VARCHAR(30),
item_num INT);
INSERT INTO redbk_orders VALUES(1,'001','201','2018-08-31 00:00:01',1);
INSERT INTO redbk_orders VALUES(2,'002','203','2018-09-02 12:00:02',2);
INSERT INTO redbk_orders VALUES(3,'003','203','2018-09-01 00:00:01',1);
INSERT INTO redbk_orders VALUES(4,'003','203','2018-09-04 09:10:30',1);
CREATE TABLE redbk_favorites(
id INT,
user_id VARCHAR(10),
item_id INT,
fav_time VARCHAR(30));
INSERT INTO redbk_favorites VALUES(1,'001','201','2018-08-31 00:00:01');
INSERT INTO redbk_favorites VALUES(2,'002','202','2018-09-02 12:00:02');
INSERT INTO redbk_favorites VALUES(3,'003','204','2018-09-01 00:00:01');

解答思路：
1.将user_id和item_id两个主键进行关联；
2.根据是否关联上可以知道是否购买和收藏；
3.full JOIN 全外连接可以得到结果，但mysql要使用union来实现（union all不去重）。

SELECT o.user_id,o.item_id,
1 AS '已购买',
CASE WHEN f.item_id IS NULL THEN 1 ELSE 0 END AS '购买未收藏',
0 AS '收藏未购买',
CASE WHEN f.item_id IS NOT NULL THEN 1 ELSE 0 END AS '收藏且购买'
FROM redbk_orders o LEFT JOIN redbk_favorites f
ON o.user_id=f.user_id AND o.item_id=f.item_id
UNION
SELECT f.user_id,f.item_id,
CASE WHEN o.item_id IS NOT NULL THEN 1 ELSE 0 END AS '已购买',
0 AS '购买未收藏',
CASE WHEN o.item_id IS NULL THEN 1 ELSE 0 END AS '收藏未购买',
CASE WHEN o.item_id IS NOT NULL THEN 1 ELSE 0 END AS '收藏且购买'
FROM redbk_favorites f
LEFT JOIN redbk_orders o ON o.user_id=f.user_id AND o.item_id=f.item_id
ORDER BY user_id,item_id;

用到了CASE WHEN  条件 THEN 成立结果 ELSE 失败结果 END

 

三、 滴滴面试题(数据太多待补)

提取2020年8月和9月，每个月北京市新老司机（首单日期在当月为新司机）的司机数、在线时长和TPH（订单量/在线时长）数据。
解题思路：
a.关键在于如何对新老司机做查询
新老司机：用IF做判断，若当日日期=首次完成订单时间，为新司机，否则为老司机。
代码如下：

SELECT a.*,IF(DATE_FORMAT(DATE,'%Y-%m')=DATE_FORMAT(a.'首次完成订单时间','%Y-%m'),'新司机','老司机')AS 新老司机
FROM '司机数据'a
WHERE (a.'日期' BETWEEN '2020-08-01' AND '2020-09-30') AND a.'城市id'='100000';

 b.最终代码
思考：为何不联结表一起计算新老司机数量、订单量、在线时长？
1，由于司机一天内可能会接多个订单，若直接关联，司机id将不唯一，会导致老司机列计数出现重复，计数结果不准确；
2，在线时长表中没给出每个订单所花时间，只给出当天所有订单共花时间。

WITH temp1 AS(#求新老司机数量
SELECT t.'城市id', MONTH(t.'日期')AS 月份,
SUM(IF(新老司机='新司机',1,0))AS 新司机数,
SUM(IF(新老司机='老司机',1,0))AS 老司机数
FROM
(SELECT a.*,IF(DATE_FORMAT(DATE,'%Y-%m')=DATE_FORMAT(a.'首次完成订单时间','%Y-%m'),
'新司机','老司机')AS 新老司机
FROM '司机数据'a
WHERE (a.'日期' BETWEEN '2020-08-01' AND '2020-09-30') AND a.'城市id'='100000';)AS t
GROUP BY MONTH(t.'日期')),
temp2 AS(#求订单量
SELECT MONTH(a.'日期')AS 月份,COUNT(a.'订单id')AS 订单量
FROM '订单数据'a
LEFT JOIN '司机数据'b ON a.司机id=b.司机id
WHERE (a.'日期'BETWEEN '2020-08-01' AND '2020-09-30')AND b.'城市id'='100000'
GROUP BY MONTH(a.'日期')),
temp3 AS(#求在线时长
SELECT MONTH(a.'日期')AS 月份,SUM(a.'在线时长')AS 在线时长
FROM '在线时长数据'a
LEFT JOIN '司机数据'b ON a.司机id=b.司机id
WHERE (a.'日期'BETWEEN '2020-08-01' AND '2020-09-30')AND b.'城市id'='100000'
GROUP BY MONTH(a.'日期'))
SELECT temp1.城市id,temp1.月份,temp1.新司机数,temp1.老司机数,temp2.订单量,temp3.在线时长,temp2.订单量/temp3.在线时长 TPH
FROM temp1
INNER JOIN temp2
ON temp1.月份=temp2.月份
INNER JOIN temp3
ON temp3.月份=temp2.月份

①这里补充日期格式化函数DATE_FORMAT()

%Y-%m-%d  2017/4/30

%y-%m-%d  17/4/30   区分大小写，其他参考：DATE_FORMAT函数用法_Lambdas的博客-CSDN博客_date_format

②每一步用temp临时表储存，最后联结查找。

四、网易面试题-用户复购率(数据待补)

1.查出每个用户第一单的消费金额
方法一：使用窗口函数ROW_NUMBER()OVER()

这里补充：1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次 1,2,2,4；

                  2.dense_rank函数：即使存在相同位次的记录，也不会跳过之后的位次 1,2,2,3；

                  3.row_number函数：赋予唯一的连续位次 1,2,3,4。

SELECT a.userid,a.amount
FROM (SELECT*,ROW_NUMBER()OVER(PARTITION BY userid ORDER BY paytime)AS rn FROM netease_order)AS a
WHERE a.rn=1;

 方法二：使用变量

SELECT b.*
FROM(SELECT a.*,CASE WHEN @userid=userid THEN @rh=rh+1
                WHEN @userid:=userid THEN @rh:=1 END AS rn
     FROM netease_order a,(SELECT @userid:=null,@rh:=0)t ORDER BY a.userid,paytime) b
WHERE b.rh=1
ORDER BY b.userid

 这里@表示变量   ：表示赋值

方法三：使用分组求最小值

SELECT t.userid,t.first_time,b.amount
FROM(SELECT userid,MIN(paytime)AS first_time FROM netease_order GROUP BY userid)t
INNER JOIN netease_order b ON t.userid=b.userid AND t.first_time=b.paytime;

2.查出每个月的新客数（新客指在严选首次支付的用户），当月有复购的新客数，新客当月复购率（当月有复购的新客数/月新客数）

WITH temp1 AS(#1.找出每个用户第一次下单的时间和年月
SELECT userid,MIN(paytime),DATE_FORMAT(paytime,'%Y-%m')'年月'
FROM netease_order GROUP BY userid),
temp2 AS(#2.找出每月的新客户数目
SELECT userid,年月,COUNT(userid)AS'新客户数'
FROM temp1 GROUP BY 年月),
temp3 AS(#3.1 找出当月有复购的新客户数，第一步找出用户在第一次下单的那个月的购买次数
SELECT n.userid,COUNT(n.orderid)AS'次数',年月
FROM netease_order n
INNER JOIN temp1 ON  n.userid=temp1.userid AND temp1.年月=DATE_FORMAT(paytime,'%Y-%m')
GROUP BY temp1.userid),
temp4 AS(#3.2 第二步是将有复购的新客筛选出来（购买次数>=2)
SELECT 年月,temp3.userid,COUNT(temp3.userid)AS'有复购的新客户数目'
FROM temp3 WHERE 次数>=2 GROUP BY temp3.年月),
temp5 AS(#计算复购率
SELECT temp2.年月,temp2.新客户数,IFNULL(temp4.有复购的新客户数目,0),IFNULL(temp4.有复购的新客户数目/temp2.新客户数,0) 复购率
FROM temp2 LEFT JOIN temp4 ON temp2.年月=temp4.年月)
SELECT * FROM temp5;

五、支付宝面试题-求支付金额在前20%的用户(数据待补)

思路：借助窗口函数ntile将每个用户和对应的支付金额分成5组（这样每组就有1/5），取分组排名第一的用户组即前支付金额在前20%。

SELECT t.user_name,t.all_pay_amount
FROM(SELECT user_name,SUM(pay_amount)all_pay_amount,NTILE(5) OVER (ORDER BY SUM(pay_amount) DESC) n FROM user_sales_table GROUP BY user_name)t
WHERE t.n=1;

六、中金财富证券互联网金融部面试题(数据待补)

统计：每个客户，在签约订单之前（不包括签约当日），在每个页面的访问次数，在每个点击事件的触发次数。
提示说明：
①所统计的是：客户在签约（购买）投顾服务订单之前所访问的各个页面的次数+在客户在签约（购买）投顾服务订单之前所点击的各个事件的次数；
②某人的页面访问次数的统计：在页面访问表（page_visit_test_201909)中的任意一条数据，代表某客户（“客户编号”字段）在某日（“页面访问的时间”字段）访问了某页面（“访问的页面”字段）1次，计数为1；
③某人的点击事件次数的统计：在点击事件表（event_visit_test_201909)中的任意一条数据，代表某客户（“客户编号”字段）在某日（“事件发生时间”字段）点击了某事件（“事件名称”字段）1次，计数为1；
④最终将每个客户访问各页面的次数和点击各事件的次数同时展示在一个结果表中，展示形式如下：

客户编号找投顾选组合投顾观点详情页投顾观点评论页投顾观点列表

投顾

个人页

投顾组合评论页投顾组合详情页

互联网

投顾首页

是否购买
302474471110010001
101770172120000001

⑤最后一个字段“是否购买”的判断条件是：如果在订单数据中能够找到对应的客户（编号），且“组合名称”不为空，则代表该客户签约（购买）了投顾服务（产品）；
⑥如遇到重复购买的人，则第一次购买前的行为数据为：9月1日至第一次购买前的行为数据（统计）；第二次购买前的行为数据为：第一次购买和第二次购买之间用户行为数据（统计）；以此类推。

原始数据 三张表：页面访问数据，点击事件触发数据，订单数据

可以先进行一些数据查看这里没有数据略过

筛选出在9月份之后真正签约的客户

SELECT *FROM order_test_201909
WHERE 签约日期>='2019-09-01'AND 组合名称 IS NOT NULL ORDER BY 客户编号,签约日期;

区分复购和非复购的用户

SELECT*,ROW_NUMBER()OVER(PARTITION BY 客户编号 ORDER BY 签约日期) rn
FROM(SELECT * FROM order_test_201909 WHERE 签约日期>='2019-09-01' AND 组合名称 IS NOT NULL ORDER BY 客户编号,签约日期) t
ORDER BY 客户编号,签约日期

第一次购买前的行为数据为：9月1日至第一次购买前的行为数据（统计）

SELECT*FROM(
SELECT*,ROW_NUMBER()OVER(PARTITION BY 客户编号 ORDER BY 签约日期) rn FROM(SELECT * FROM order_test_201909 WHERE 签约日期>='2019-09-01' AND 组合名称 IS NOT NULL ORDER BY 客户编号,签约日期) t ORDER BY 客户编号,签约日期) o
INNER JOIN page_visit_test_201909 p ON o.客户编号=p.客户编号
WHERE rn=1

行转列

SELECT o.客户编号,
SUM(IF(访问的页面='找投顾' AND 页面访问时间<签约日期,1,0)) AS 找投顾,
SUM(IF(访问的页面='选组合' AND 页面访问时间<签约日期,1,0)) AS 选组合,
SUM(IF(访问的页面='投顾观点详情页' AND 页面访问时间<签约日期,1,0)) AS 投顾观点详情页,
SUM(IF(访问的页面='投顾观点评论页' AND 页面访问时间<签约日期,1,0)) AS 投顾观点评论页,
SUM(IF(访问的页面='投顾观点列表' AND 页面访问时间<签约日期,1,0)) AS 投顾观点列表,
SUM(IF(访问的页面='投顾个人页' AND 页面访问时间<签约日期,1,0)) AS 投顾个人页,
SUM(IF(访问的页面='投顾组合评论页' AND 页面访问时间<签约日期,1,0)) AS 投顾组合评论页,
SUM(IF(访问的页面='投顾组合详情页' AND 页面访问时间<签约日期,1,0)) AS 投顾组合详情页,
SUM(IF(访问的页面='互联网投顾首页' AND 页面访问时间<签约日期,1,0)) AS 互联网投顾首页,
1 AS 是否购买
FROM(SELECT*,ROW_NUMBER()OVER(PARTITION BY 客户编号 ORDER BY 签约日期) rn FROM(SELECT * FROM order_test_201909 WHERE 签约日期>='2019-09-01' AND 组合名称 IS NOT NULL ORDER BY 客户编号,签约日期) t ORDER BY 客户编号,签约日期) o
INNER JOIN page_visit_test_201909 p ON o.客户编号=p.客户编号
WHERE rn=1 GROUP BY o.客户编号

第二次购买前的行为数据为：第一次购买和第二次购买之间用户行为数据（统计）
添加字段，订单1的签约日期，订单2的签约日期

SELECT*,IF(rn=1,签约日期,NULL)第一次签约日期,LAG(IF(rn=1,签约日期,NULL),1)OVER(ORDER BY 客户编号,签约日期)第一次签约日期_,IF(rn=2,签约日期,NULL)第二次签约日期
FROM(SELECT*,ROW_NUMBER()OVER(PARTITION BY 客户编号 ORDER BY 签约日期) rn FROM(SELECT * FROM order_test_201909 WHERE 签约日期>='2019-09-01' AND 组合名称 IS NOT NULL ORDER BY 客户编号,签约日期) t
ORDER BY 客户编号,签约日期) t1

LAG(IF(rn=1,签约日期,NULL),1)OVER(ORDER BY 客户编号,签约日期)第一次签约日期_   可返回第一次签约日期列前一行的数据。视觉上是下移一位

补充：LEAD(field, num, defaultvalue) field 需要查找的字段，num 往后查找的 num 行的数据，defaultvalue 没有符合条件的默认值

LAG(field, num, defaultvalue) field 需要查找的字段，num 往前查找的 num 行的数据，defaultvalue 没有符合条件的默认值

第一次购买和第二次购买之间用户的行为特征数据

SELECT o.客户编号,
SUM(IF(访问的页面='找投顾' AND 页面访问时间<签约日期,1,0)) AS 找投顾,
SUM(IF(访问的页面='选组合' AND 页面访问时间<签约日期,1,0)) AS 选组合,
SUM(IF(访问的页面='投顾观点详情页' AND 页面访问时间<签约日期,1,0)) AS 投顾观点详情页,
SUM(IF(访问的页面='投顾观点评论页' AND 页面访问时间<签约日期,1,0)) AS 投顾观点评论页,
SUM(IF(访问的页面='投顾观点列表' AND 页面访问时间<签约日期,1,0)) AS 投顾观点列表,
SUM(IF(访问的页面='投顾个人页' AND 页面访问时间<签约日期,1,0)) AS 投顾个人页,
SUM(IF(访问的页面='投顾组合评论页' AND 页面访问时间<签约日期,1,0)) AS 投顾组合评论页,
SUM(IF(访问的页面='投顾组合详情页' AND 页面访问时间<签约日期,1,0)) AS 投顾组合详情页,
SUM(IF(访问的页面='互联网投顾首页' AND 页面访问时间<签约日期,1,0)) AS 互联网投顾首页,
1 AS 是否购买
FROM(SELECT*,IF(rn=1,签约日期,NULL)第一次签约日期,LAG(IF(rn=1,签约日期,NULL),NULL,1)OVER(ORDER BY 客户编号,签约日期)第一次签约日期_,IF(rn=2,签约日期,NULL)第二次签约日期
     FROM(SELECT*,ROW_NUMBER()OVER(PARTITION BY 客户编号 ORDER BY 签约日期) rn FROM(SELECT * FROM order_test_201909 WHERE 签约日期>='2019-09-01' AND 组合名称 IS NOT NULL ORDER BY 客户编号,签约日期) t ORDER BY 客户编号,签约日期) t1)o
INNER JOIN page_visit_test_201909 p ON o.客户编号=p.客户编号
WHERE rn=2 AND 页面访问时间 BETWEEN 第一次签约日期_ AND 第二次签约日期 GROUP BY o.客户编号;

七、游戏类面试题

1.将数据生产年、月、及对应每月实际数据（题中数据为累加，例如需统计2016年2月数据，需要将1月份扣减）
数据准备：
创建promotion_data表

CREATE TABLE promotion_data(
log_data VARCHAR(255),
accumulated_impressions VARCHAR(255));
插入数据
INSERT INTO promotion_data VALUES('2015-12-31','900');
INSERT INTO promotion_data VALUES('2016-01-01','1000');
INSERT INTO promotion_data VALUES('2016-01-02','1100');
INSERT INTO promotion_data VALUES('2016-01-03','1200');
INSERT INTO promotion_data VALUES('2016-01-04','1300');
INSERT INTO promotion_data VALUES('2016-01-05','1400');
INSERT INTO promotion_data VALUES('2016-01-06','1500');
INSERT INTO promotion_data VALUES('2016-01-07','1600');
INSERT INTO promotion_data VALUES('2016-01-08','1700');
INSERT INTO promotion_data VALUES('2016-01-09','1800');
INSERT INTO promotion_data VALUES('2016-01-10','1900');
INSERT INTO promotion_data VALUES('2016-01-11','2000');
INSERT INTO promotion_data VALUES('2016-01-12','2100');
INSERT INTO promotion_data VALUES('2016-01-13','2200');
INSERT INTO promotion_data VALUES('2016-01-14','2300');
INSERT INTO promotion_data VALUES('2016-01-15','2400');
INSERT INTO promotion_data VALUES('2016-01-16','2500');
INSERT INTO promotion_data VALUES('2016-01-17','2600');
INSERT INTO promotion_data VALUES('2016-01-18','2700');
INSERT INTO promotion_data VALUES('2016-01-19','2800');
INSERT INTO promotion_data VALUES('2016-01-20','2900');
INSERT INTO promotion_data VALUES('2016-01-21','3000');
INSERT INTO promotion_data VALUES('2016-01-22','3100');
INSERT INTO promotion_data VALUES('2016-01-23','3200');
INSERT INTO promotion_data VALUES('2016-01-24','3300');
INSERT INTO promotion_data VALUES('2016-01-25','3400');
INSERT INTO promotion_data VALUES('2016-01-26','3500');
INSERT INTO promotion_data VALUES('2016-01-27','3600');
INSERT INTO promotion_data VALUES('2016-01-28','3700');
INSERT INTO promotion_data VALUES('2016-01-29','3800');
INSERT INTO promotion_data VALUES('2016-01-30','3900');
INSERT INTO promotion_data VALUES('2016-01-31','4000');
INSERT INTO promotion_data VALUES('2016-02-01','4100');
INSERT INTO promotion_data VALUES('2016-02-02','4200');
INSERT INTO promotion_data VALUES('2016-02-03','4300');

 参考答案：

--新增一个字段data2,data2的数据是根据data1向下移动一位得到
SELECT log_data ,accumulated_impressions AS data1,LAG(accumulated_impressions,1)OVER(ORDER BY log_data ASC)data2
FROM promotion_data
--data1-data2获得每天实际新增
SELECT*,DATE_FORMAT(log_data,'%Y-%m')ym,(data1-data2)AS day_increment
FROM(SELECT log_data ,accumulated_impressions AS data1,LAG(accumulated_impressions,1)OVER(ORDER BY log_data ASC)data2 FROM promotion_data)t1
--分组年月，聚合每天实际新增得到每月实际新增
SELECT ym,SUM(day_increment)month_increment
FROM(SELECT*,DATE_FORMAT(log_data,'%Y-%m')ym,(data1-data2)AS day_increment FROM(SELECT log_data ,accumulated_impressions AS data1,LAG(accumulated_impressions,1)OVER(ORDER BY log_data ASC)data2 FROM promotion_data)t1)t2
GROUP BY ym;

 

2.查询‘<7days’表示此玩家每一行日期的前7天之内玩过游戏
创建play_time表

CREATE TABLE play_time(
play_name VARCHAR(255),
play_date DATE,
play_length INT);
插入数据
INSERT INTO play_time VALUES('cc','2020/1/31',180);
INSERT INTO play_time VALUES('cc','2020/2/1',150);
INSERT INTO play_time VALUES('mb','2020/2/1',120);
INSERT INTO play_time VALUES('nb','2020/2/2',133);
INSERT INTO play_time VALUES('cca','2020/2/3',160);
INSERT INTO play_time VALUES('fg','2020/2/4',154);
INSERT INTO play_time VALUES('mb','2020/2/6',173);
INSERT INTO play_time VALUES('fg','2020/2/12',220);
INSERT INTO play_time VALUES('nb','2020/2/12',220);
INSERT INTO play_time VALUES('cca','2020/2/16',201);
INSERT INTO play_time VALUES('fg','2020/2/23',99);

添加辅助列play_date_

SELECT *,LAG(play_date,1)OVER(PARTITION BY play_name ORDER BY play_date)play_date_
FROM play_time
#判断play_date和play_date_的日期是否小于7天
SELECT play_name,play_date,play_length,CASE WHEN play_date_ IS NULL THEN 'no'
                                            WHEN DATEDIFF(play_date,play_date_)<7 THEN 'yes'ELSE 'no' END '<7 days'
FROM(SELECT *,LAG(play_date,1)OVER(PARTITION BY play_name ORDER BY play_date)play_date_ FROM play_time)t
ORDER BY  play_name,play_date; 

DATEDIFF() 函数返回两个日期之间的时间。更多参考：SQL Server DATEDIFF() 函数

八、电商类面试题

数据准备

建表
CREATE TABLE trans(
counter_id CHAR(20) COMMENT"柜台ID",
counter_name CHAR(20) COMMENT"柜台名称",
customer_id CHAR(20) COMMENT"客户ID",
order_id CHAR(20) COMMENT"订单ID",
product_id CHAR(20) COMMENT"产品ID",
product_name_cn CHAR(20) COMMENT"产品名称",
quantity INT COMMENT"购买数量",
unit_price DECIMAL(8,1) COMMENT"产品单价",
purchase_date DATE COMMENT"购买时间",
brand CHAR(20) COMMENT"购买品牌");

CREATE TABLE PROFILE(
customer_id CHAR(20) COMMENT"客户ID",
first_pur_date DATE COMMENT"购买时间",
age INT COMMENT"客户年龄");
插入数据
INSERT INTO trans VALUES
('offline001','counterA','customer001','20180214001','product001','SK-II神仙水',1,1590.0,'2018-02-14','SK-II'),
('offline002','counterB','customer002','20190315002','product002','雅诗兰黛小棕瓶精华液',3,899.0,'2019-03-15','雅诗兰黛'),
('offline003','counterC','customer003','20190416003','product003','兰蔻小黑瓶基底精华液',2,1080.0,'2019-04-16','LANCOME'),
('offline004','counterD','customer004','20190517004','product004','海蓝之谜修护精粹液',3,1150.0,'2019-05-17','LAMER'),
('offline005','counterE','customer005','20190618005','product005','玉兰油淡斑小白瓶精华',10,498.0,'2019-06-18','OLAY'),
('offline001','counterA','customer001','20180501012','product001','SK-II神仙水',2,1590.0,'2018-05-01','SK-II'),
('offline004','counterD','customer001','20180618065','product004','海蓝之谜修护精粹液',2,1150.0,'2018-06-18','LAMER'),
('offline004','counterD','customer001','20181110065','product004','海蓝之谜修护精粹液',6,1150.0,'2018-11-10','LAMER'),
('offline004','counterD','customer001','20190213033','product004','海蓝之谜修护精粹液',8,1150.0,'2018-06-18','LAMER'),
('offline002','counterB','customer002','20190429088','product002','雅诗兰黛小棕瓶精华液',5,899.0,'2019-03-15','雅诗兰黛'),
('online001','NA','customer002','20190616125','product002','雅诗兰黛小棕瓶精华液',6,899.0,'2019-06-16','雅诗兰黛'),
('offline003','counterC','customer003','20190617015','product003','兰蔻小黑瓶基底精华液',1,1080.0,'2019-06-17','LANCOME'),
('offline004','counterD','customer004','20190901024','product004','海蓝之谜修护精粹液',3,1150.0,'2019-09-01','LAMER'),
('offline005','counterE','customer005','20190818035','product005','玉兰油淡斑小白瓶精华',3,498.0,'2019-08-18','OLAY'),
('offline005','counterE','customer005','20191018077','product005','玉兰油淡斑小白瓶精华',5,498.0,'2019-10-18','OLAY'),
('offline002','counterB','customer005','20191110085','product005','雅诗兰黛小棕瓶精华液',2,899.0,'2019-11-10','雅诗兰黛');

INSERT INTO PROFILE VALUES
('customer001','2018-02-14',22),
('customer002','2019-03-15',25),
('customer003','2019-04-16',28),
('customer004','2019-05-17',33),
('customer005','2019-06-18',26);

 

1.计算每个客户在2019年的总花费(sales)，购买的订单数frequency，每单平均花费AUS及
每单平均购买的数量PT

SELECT customer_id,SUM(quantity*unit_price)AS sales,COUNT(order_id)AS frequency,SUM(quantity*unit_price)/COUNT(order_id) AS aus,SUM(quantity)/COUNT(order_id)AS pt
FROM trans 
WHERE YEAR(purchase_date)=2019 
GROUP BY customer_id;

2.计算每一类客户在2019年每月产生的销售额(即购买金额>0),及发生购买的人数(客户类型分为新客、老客；新客：2019年产生第一次购买，老客：2019年之前已经购买过)

1.增加老客新标签
SELECT a.*,b.*,CASE WHEN YEAR(b.first_pur_date)=2019 THEN 'new' ELSE 'old' END AS customer_type
FROM trans a LEFT JOIN PROFILE b ON a.customer_id=b.customer_id
WHERE YEAR(a.purchase_date)=2019;
2.分类统计即可
SELECT DATE_FORMAT(purchase_date,'%Y-%m')AS date_ym,customer_type,SUM(quantity*unit_price)AS sale_money,COUNT(*)AS shop_people
FROM(SELECT a.*,CASE WHEN YEAR(b.first_pur_date)=2019 THEN 'NEW' ELSE 'OLD' END AS customer_type FROM trans a LEFT JOIN PROFILE bON a.customer_id=b.customer_id WHERE YEAR(a.purchase_date)=2019)t
GROUP BY MONTH(purchase_date),customer_type;

3.现要给2019年新客发送A,B小样，线上新客发放A小样，线下新客发放B小样，请编写程序抽取相应的名单。字段COUNTERID:offine线下，online线上

SELECT DISTINCT t.customer_id,CASE WHEN t.counter_id LIKE 'online%' THEN 'A' ELSE 'B'END '小样类型'
FROM(SELECT a.*,CASE WHEN YEAR(b.first_pur_date)=2019 THEN 'NEW' ELSE 'OLD' END AS customer_type FROM trans a LEFT JOIN PROFILE b ON a.customer_id=b.customer_id WHERE YEAR(a.purchase_date)=2019)t
WHERE t.customer_type='new';

4.展示每种品牌的销售数量，要求排序，并排出每家商铺对应占该品牌的销售额占比情况

-- 每种品牌的销售数量 销售额
SELECT brand,SUM(quantity)AS sum_quantity,SUM(quantity*unit_price)AS sales
FROM trans 
GROUP BY brand
-- 每家店铺的销售数量 销售额
SELECT brand,counter_name,SUM(quantity)AS sum_quantity,SUM(quantity*unit_price)AS sales
FROM trans 
GROUP BY brand,counter_name
-- 结合以上，计算占比
SELECT a.brand,a.counter_name,a.sales/b.sales AS'销售额占比',b.sum_quantity AS'销售数量'
FROM(SELECT brand,counter_name,SUM(quantity)AS sum_quantity,SUM(quantity*unit_price)AS sales FROM trans GROUP BY brand,counter_name)a 
LEFT JOIN(SELECT brand,SUM(quantity)AS sum_quantity,SUM(quantity*unit_price)AS sales FROM trans GROUP BY brand)b ON a.brand=b.brand
ORDER BY 销售数量 DESC;

                                                                                                                                 

好困啊_
关注
大厂面试常见的几道SQL题，看你能答吗?
xiaoxijinger的博客
06-09 674
收集了几道比较常见的SQL面试题，在不看底部参考答案的情况下，看自己能做对几道。 1.用一条SQL语句查询出每门课都大于80分的学生姓名 答案： --方法一： select distinct name from table where name not in ( select distinct name f rom table where fenshu<=80 ) --方法二： select name from table group by name having min(fenshu)&g
【数据分析面试】大厂高频SQL笔试题（四）
licent2011的博客
11-17 674
写在前面 数据分析SQL笔试题系列第4篇来啦！之前笔试题的文章如果没有看可以戳： 无论你是刚毕业的职场小鲜肉、还是想转行数据分析的小白玩家，只要想进入数据分析的行业，都逃不过数据分析面试的考验，这里最重要也是最关键的一关就是SQL笔试了，不过不用担心，结合作者6年+的工作和面试经验，系统全面地整理了数据分析面试中那些高频出现的以及各大厂的SQL笔试题，学习了这些笔试题的常见套路和解法，把这些题目都刷一遍，在接下来的笔试中应该可以一往无前，收割offer啦！ SQL笔试不仅考查代码的熟练程度，更多的考查的是对
史上最全的大厂Mysql面试题在这里
民工哥的博客
04-26 3372
1、MySQL的复制原理以及流程 基本原理流程，3个线程以及之间的关联； 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中； 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中； 从：sql执行线程——执行relay log中的语句； 2、M...
面试 SQL整理 常见的SQL面试题：大厂经典60题（一）
最新发布
2401_83014911的博客
07-26 580
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。作用：用于存取数据、查询、更新和管理关系数据库系统。MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
经典面试题-大厂SQL题目
Jason王祖龙
03-28 4701
1、 如何用一个SQL找出部门下所有员工的平均工资大于某个数（例如20000元）的所有部门？ 正确答案: select depno, avg(sal) as avgsal from emp group by depno having avgsal > 20000 2、 两张结构一模一样的表，只是放的不同时间的数据，怎么用一个SQL捞出来后对某个数值字段做做平均值统计？ 正确答案: select AVG(table3.mean) from (SELECT mean FROM table1 UNION
常见大厂SQL笔试面试题总结
qq_34911436的博客
10-12 1755
Hive 常见笔试题 一、累计次数 1.1、 编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数 数据集 A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,16 A,2015-03,22 B,2015-03,23 B
sql
PersonalM的博客
11-20 368
（1）找出连续7天登陆，连续30天登陆的用户（小红书笔试，电信云面试），最大连续登陆天数的问题 --窗口函数 （2）求连续点击三次的用户数，中间不能有别人的点击 ，最大连续天数的变形问题（腾讯微保面试）--窗口函数 （3）计算除去部门最高工资，和最低工资的平均工资（字节跳动面试）--窗口函数 （4）留存的计算，和累计求和的计算 --窗口函数，自联结（pdd面试） （5）AB球队得分流水表，得到连续三次得分的队员名字 和每次赶超对手的球员名字,（pdd面试） 把这几类题型吃透，再也不怕手撕SQL和.
学习笔记：SQL常考知识点
m0_61136860的博客
09-19 844
题源代码来自：数据分析-全网最全SQL面试真题(做完，面试再也不虚了-划分了知名互联网大厂、不同行业背景（如电商、游戏）、常考知识点三大部分的SQL真题）_哔哩哔哩_bilibili欢迎大家一起交流学习，课件资料会在数据蛙SQL面试题交流群持续更新~加群方式-加V：lkx941013，备注:SQL视频持续更新inghttps://www.bilibili.com/video/BV1Rh411B7sN?p=15&share_source=copy_web【724补】过好久了，再重新整理复习一下|ᴥ•.
Java 进阶之路，互联网一线大厂面试真题解析、经验分享.zip
10-22
在Java的进阶学习过程中，理解并掌握互联网一线大厂面试中的常见问题和解决策略是至关重要的。这个压缩包文件“Java 进阶之路，互联网一线大厂面试真题解析、经验分享.zip”包含了深入探讨Java技术以及面试技巧的...
MySQL最全整理（面试题+笔记+导图），面试大厂不再被MySql难倒
2401_84435000的博客
04-21 950
网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**
2022年SQL经典面试题总结（带解析）
热门推荐
hahazz233的博客
06-27 1万+
一、选择题（1）基础题1、要求删除商品表中价格大于3000的商品，下列SQL语句正确的是（）A、DELETE FROM 商品 WHERE 价格>3000B、DELETE * FROM 商品 WHERE 价格>3000C、DELETE FROM 商品D、UPDATE 商品 SET * =NULL WHERE 价格>3000正确答案: A答案解析：这道题考察的是delete语法DELETE FROM table_nameWHERE some_column=some_value;2、在book表中，将工具书类型（
大厂面试sql手撕题目总结
weixin_41767872的博客
05-06 1134
【代码】大厂面试sql手撕题目总结。
【吊打面试官】Mysql大厂高频面试题！！！
码猿技术专栏
04-21 1378
前言 前几天有读者找到我，说想要一套全面的Mysql面试题，今天陈某特地为她写了一篇。文章的目录如下：Mysql面试题 什么是SQL？什么是MySQL？数据库三大范式是什么？mysql有...
网易mysql笔试题_MySql进阶面试题
weixin_28911533的博客
01-19 254
1. 数据库三范式是什么?1. 第一范式(1NF)：字段具有原子性,不可再分。(所有关系型数据库系统都满足第一范式数据库表中的字段都是单一属性的，不可再分)2. 第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关...
大数据SQL经典面试题系列(3) -网易严选/考拉数据分析SQL题
寒暄的博客
08-26 1099
查询每个用户第一单的消费金额 现在数据库中有一张用户交易表order，其中有userid（用户ID）、amount（消费金额）、paytime（支付时间），请写出对应的SQL语句，查出每个用户第一单的消费金额。 select a.userid,a.amount from (select * ,rank() over(partition by userid order by paytime) as paytime_rank from order ) as a where paytime_rank=1 查询
2023年SQL大厂高频实战面试题（详细解析）
腾讯云云开发布道师，慕课网合作讲师
01-05 1796
再通过DepartmentId 字段连接员工表Employee 和部门表Department，连接完成后，根据上一步查出的部门ID（DepartmentId）以及对应的最高工资，查找所有对应的所有员工的名字。这道题考察的是子查询，我们想要查出没有订购过的客户，就先在订单表Orders中，查出已经订购过的客户，再在客户表Customers中排除掉这些客户，就很容易知道谁从未订购过。这道题主要考察的是“相关子查询”，在相关子查询中，子查询是在主查询每一条记录层面上依次进行的，子查询依赖主查询。
货拉拉sql互联网大厂面试真题在线练习
valkyrja110的博客
09-20 1841
货拉拉sql面试真题在线练习一、sql刷题目的二、互联网大厂货拉拉sql面试题1.货拉拉sql数据表介绍2.货拉拉订单练习题sql语句（1）用车方和司机被禁止(banned=1)的比率分别为？明确问题：解题思路：参考答案答案思考时间（2）2020年1月25日的订单完成率为？明确问题：解题思路：参考答案：答案：思考时间：（3）用车至少两次，且主动取消过至少1次的用车方有多少名？明确问题：解题思路：参考答案：答案：思考时间：（4）北京、上海的非禁止用户的用车取消率分别为？明确问题：解题思路：参考答案:答案:思考
Oracle学习笔记：SQL与PL/SQL基础
"Oracle笔记，包括SQL和PL/SQL的学习要点，涵盖了数据库对象、数据类型、常用语句等，部分信息来源于网络和动力节点的视频教程。" 在Oracle数据库管理系统中，SQL（Structured Query Language）和PL/SQL是两种至关...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值