笔试题之编写SQL按要求查询用户阅读行为数据

紧张源于恐惧，恐惧源于未知。

文章目录 前言一、SQL题目二、当时作答结果三、复盘（一）建表并自定义插入数据（二）正确解答（三）答错原因分析 总结

---

前言

分享本人一次失败的笔试经历，供各位小伙伴参考。

---

一、SQL题目

表user_read, 记录有访问用户每天的行为(有一个行为表里就会产生一行数据)。有以下几列： dateStr: 日期(天) uin: 用户ID docId: 用户的阅读的内容ID readTime: 每一篇内容对应的阅读时长(s)

第一题：20210820当天有来的且下一天又来的用户 第二题：20210820当天阅读时长大于平均阅读时长人群的次日留存 第三题：20210820-20210822 每天阅读时间最长的用户以及对应的时长 (用窗口函数）

二、当时作答结果 -- 第一题 SELECT uin FROM tb1 WHERE dateStr = '20210821' AND uin IN ( SELECT uin FROM tb1 WHERE dateStr = '20210820' GROUP BY uin ) GROUP BY uin; -- 第二题 SELECT t4.uin / t3.uin FROM ( SELECT t1.uin FROM ( SELECT uin ,readTime FROM tb1 WHERE dateStr = '20210820' ) t1 LEFT OUTER JOIN ( SELECT AVG(readTime) AS avg_readTime FROM tb1 WHERE dateStr = '20210820' ) t2 ON 1 = 1 WHERE t1.readTime > t2.avg_readTime GROUP BY t1.uin ) t3 LEFT OUTER JOIN ( SELECT uin FROM tb1 WHERE dateStr = '20210821' GROUP BY uin ) t4 ON t3.uin = t4.uin; -- 第三题 SELECT t1.dateStr ,t1.uin ,t1.readTime ,ROW_NUMBER OVER(PARTITION BY t1.dateStr ORDER BY readTime DESC) rn FROM ( SELECT dateStr ,uin ,SUM(readTime) AS readTime FROM tb1 WHERE (dateStr >= '20210820' AND dateStr <= '20210822') GROUP BY dateStr ,uin ) t1 WHERE rn = 1; 三、复盘 （一）建表并自定义插入数据 create table user_read ( dateStr varchar(10) ,uin varchar(10) ,docId varchar(10) ,readTime int ); insert into user_read values ('20210820','1','001',50), ('20210820','1','002',5), ('20210821','1','001',20), ('20210822','1','003',30), ('20210820','2','001',35), ('20210821','2','001',50), ('20210822','2','006',66), ('20210820','3','002',52), ('20210820','3','002',58), ('20210820','3','002',31);

（二）正确解答 -- 第一题 SELECT uin FROM user_read WHERE dateStr = '20210821' AND uin IN ( SELECT uin FROM user_read WHERE dateStr = '20210820' GROUP BY uin ) GROUP BY uin ;

-- 第二题 WITH lt_avg AS ( SELECT t1.uin FROM ( SELECT uin ,SUM(readTime) AS readTime FROM user_read WHERE dateStr = '20210820' GROUP BY uin ) t1 INNER JOIN ( SELECT AVG(readTime) AS avg_readTime FROM user_read WHERE dateStr = '20210820' ) t2 ON t1.readTime > t2.avg_readTime ) SELECT COUNT(t2.uin) / COUNT(t1.uin) AS nd_retention FROM ( SELECT uin FROM lt_avg ) t1 LEFT OUTER JOIN ( SELECT uin FROM user_read WHERE dateStr = '20210821' AND uin IN ( SELECT uin FROM lt_avg ) GROUP BY uin ) t2 ON t1.uin = t2.uin ;

-- 第三题 SELECT t1.dateStr ,t1.uin ,t1.readTime FROM ( SELECT dateStr ,uin ,SUM(readTime) AS readTime ,ROW_NUMBER() OVER(PARTITION BY dateStr ORDER BY SUM(readTime) DESC) AS rn FROM user_read WHERE (dateStr >= '20210820' AND dateStr <= '20210822') GROUP BY dateStr ,uin ) t1 WHERE rn = 1 ;

（三）答错原因分析

原因：

SQL不熟练，斋写SQL没跑不知道对错；线上共享屏幕做题的形式没有接触过，有些紧张；平时主要接触制造业的数据，用户行为数据不熟悉，造成紧张；太紧张导致没有看清楚题目。

解决方案：

刷SQL题；熟悉斋写SQL的方式；面试前根据岗位了解相关业务。

---

总结

紧张源于恐惧，恐惧源于未知。本人复盘笔试时发现自己三道题有两道题做错，很不应该。面试为线上面试的形式，面试官突然发笔试题要求共享屏幕做题，因没有数据斋写SQL，加上对业务过程不熟悉，全程很紧张，题目都看错。归根结底还是因为自己第一次参加这样的笔试形式，SQL知识也不熟练造成。各位小伙伴要多多练习，吸取教训。