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

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

---

前言

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

---

一、SQL题目

表user_read, 记录有访问用户每天的行为(有一个行为表里就会产生一行数据)。有以下几列：

1. dateStr: 日期(天)
2. uin: 用户ID
3. docId: 用户的阅读的内容ID
4. 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);

（二）正确解答

-- 第一题：20210820当天有来的且下一天又来的用户
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
;

-- 第二题：20210820当天阅读时长大于平均阅读时长人群的次日留存
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
;

-- 第三题：20210820-20210822 每天阅读时间最长的用户以及对应的时长  
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
;

（三）答错原因分析

原因：

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

解决方案：

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

---

总结

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