SQL：分享两个SQL笔试题

前言

公司招人招了四个多月了，前阵子让我出几个笔试题，这里把题目和答案分享下。

题目一：连续登录问题

user_id	dt
a	2020-09-01
a	2020-09-02
a	2020-09-05
a	2020-09-07
a	2020-09-08
a	2020-09-09
a	2020-09-10
b	2020-09-08
b	2020-09-09
c	2020-09-05
d	2020-09-04
d	2020-09-05
d	2020-09-06
d	2020-09-07

题目描述

上表是一个用户活跃表（表名为user_login），包含用户ID和活跃日期。每行数据代表该用户在当天活跃过。

现根据上表写SQL，求出在9月当中，连续活跃天数超过3天的user_id，以及其最大连续天数。

结果应为：
 

a	4
d	4

思路

本体考察逻辑能力。一般连续指标的问题，都是通过等差序列来求的。本题可根据日期进行排序，获得一个差值为1的等差序列rk，如果日期是连续的，那么连续的这段日期其实也是一个差值为1的等差序列，既然两个等差序列的差值分别相同，则两个等差序列对应的差（dt-rk）应该是一个固定值。

如题中的数据，先根据dt排序，得出rk，再用dt-rk，则可以得出dt-rk这列，如果dt-rk的值相同，则说明相同的这几条数据的dt是连续的。根据dt-rk分组，如果count值大于等于3，则说明连续登录天数大于3天。

user_id	dt	rk	dt-rk
a	2020-09-01	1	2020-08-31
a	2020-09-02	2	2020-08-31
a	2020-09-05	3	2020-09-02
a	2020-09-07	4	2020-09-03
a	2020-09-08	5	2020-09-03
a	2020-09-09	6	2020-09-03
a	2020-09-10	7	2020-09-03
b	2020-09-08	1	2020-09-07
b	2020-09-09	2	2020-09-07
c	2020-09-05	1	2020-09-04
d	2020-09-04	1	2020-09-03
d	2020-09-05	2	2020-09-03
d	2020-09-06	3	2020-09-03

 答案

select
user_id,
max(count) as count
(
    select
    user_id,
    count(1) as count
    from
    (
        select 
        user_id,
        date_sub(dt,rk) as dt_group
        from
        (
            select 
            user_id,
            dt,
            row_number() over(partition by user_id order by dt desc) as rn
            from user_login
            )
    )
    group by user_id,dt_group
    having count(1) >= 3
)
group by user_id
;

题目二：结余问题

user_id	amount	dt
a	100	2020-09-01
a	-50	2020-09-02
a	-25	2020-09-03
b	50	2020-09-01
b	50	2020-09-02
b	-50	2020-09-03

题目描述

上表是一个用户消费-充值表（user_pay）。包含用户id、当日充值/消费总额、日期三个字段。假设每天只有一行数据（即你不需要考虑当天既有充值也有消费的情况）。用户初始结余为0，请根据上表情况写SQL，求出用户每天的结余。
结果应为：

user_id	amount	dt	total
a	100	2020-09-01	100
a	-50	2020-09-02	50
a	-25	2020-09-03	25
b	50	2020-09-01	50
b	50	2020-09-02	100
b	-10	2020-09-03	90

思路

本体相较上一题简单多了，主要就是考察下sql熟练度。解法很多，使用窗口函数lag、lead都可以求。

但我给出的答案是开窗、分区、排序后直接用sum求出答案。主要考察是否了解开窗函数中，sort by和order by作用域区别。

答案

select
user_id,
amount,
dt,
sum(amount) over(partition by user_id order by dt) as total
from user_pay