百川笔试 · Aiznoyer Atlas

笔试题一：

要求

1、连续登场 5 次且每次至少进一个球

2、至少有一次又有 2 分球又有 3 分球

思路

1、按照每个时间、球员进行 group 过滤掉得分 0 的数据，并按照时间顺序只取每个球员每星期的前五条数据

2、按照星期进行聚合，得分有2有3则设置group_id为5，否则为之前的数，并将球员/星期的总分的时间固定为星期的最后一天。

3、按照 group_id = 5 的数据向前找 4 个星期，向后找 4 个星期

(吐槽一下样例数据完全没有按照每周有且只有一场比赛来)

sql

笔试题二：

要求

1、求每一天，最多有多少人同时使用 App

2、计算 App，近半年内，每一天的第 7 日留存率

思路

1、使用的判断条件为：当天时间≥登陆时间&&当天时间≤登出时间

2、如果一个用户在当天登出后又登入，应该只算一次在线（拉链表思想）

mysql

WITH t AS (
    SELECT DISTINCT
    u1.uid,
    u1.login_time,
    max( u1.logout_time ) as logout_time
FROM
    ( SELECT uid, date( login_ts ) AS login_time, date( logout_ts ) AS logout_time FROM user_log ) u1
    LEFT JOIN ( SELECT uid, date( login_ts ) AS login_time, date( logout_ts ) AS logout_time FROM user_log ) u2 ON u1.logout_time = u2.login_time 
GROUP BY
    u1.uid,
    u1.login_time
),
dr as (
    SELECT DATE(DATE_ADD((SELECT MIN(login_time) FROM t), INTERVAL seq.seq DAY)) AS date_time
    FROM (
        SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL 
        SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL 
        SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL 
        SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL 
        SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL 
        SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
    ) seq
    WHERE DATE_ADD((SELECT MIN(login_time) FROM t), INTERVAL seq.seq DAY) <= (SELECT MAX(logout_time) FROM t)
)
SELECT
    date_time,
    COUNT( DISTINCT t.uid ) AS max_users 
FROM
    dr
    LEFT JOIN t ON dr.date_time BETWEEN t.login_time 
    AND t.logout_time 
WHERE
    t.login_time <= dr.date_time AND t.logout_time >= dr.date_time 
GROUP BY
    dr.date_time 
ORDER BY
    dr.date_time;
    
-- 样例数据如下：
-- uid       login_ts           logout_ts
-- 1    2023-09-10 00:03:10 2023-09-10 00:13:15
-- 2    2023-09-10 11:12:56 2023-09-10 12:19:22
-- 1    2023-09-10 23:03:10 2023-09-11 23:45:07
-- 1    2023-09-13 23:03:10 2023-09-14 23:45:07

-- 输出结果如下：
--  date_time max_users
-- 2023-09-10   2
-- 2023-09-11   1
-- 2023-09-13   1
-- 2023-09-14   1

-- t表的作用是规约时间
-- dr表的作用是确定整个时间轴范围
-- 第二问都可以复用

思路

1、找到每天登陆的用户表，和截取的7天后登陆的用户表进行left join，如果uid相同且时间刚好大于7就设置exist_number为1，否则为0

2、聚合计算每天的用户算出比率。

mysql

1、转换为单天的用户记录
SELECT
    uid,
    date_time 
FROM
    dr
    LEFT JOIN t ON dr.date_time BETWEEN t.login_time 
    AND t.logout_time 
WHERE
    t.login_time <= dr.date_time AND t.logout_time >= dr.date_time;
-- 输出结果如下：
-- uid  date_time
-- 2    2023-09-10
-- 1    2023-09-10
-- 1    2023-09-11
-- 1    2023-09-13
-- 1    2023-09-14
-- 1    2023-09-20
……后面的步骤又是嵌套了emmm

ps：我不知道我想复杂了，还是水平不够（水平不够概率大点），看上去很简单但是限定条件屌多，有一些处理过程放到Java中会好处理很多，用点工具可以慢慢磨把这三个题磨出来，不过磨出来了贵司大概率也不会招我，所以懒得花时间了。

望下次贵司提供样例的时候稍微给多一点吧QWQ