每日SQL一练#20231024

今天也是leetcode的。

聚合函数习题

题目链接： 550. 游戏玩法分析 IV

题干信息

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键（具有唯一值的列的组合）。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

测试数据

Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释：
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

题干解析

只需要计算从首次登录日期开始至少连续两天登录的玩家的数量，而不是连续两天登录的玩家的数量。 因为只有event_date一个登录日期列，所以需要用lag或lead构造新列，最后判断玩家是否连续登录。 还需要构造row_number列用于判断用户的首次登录的日期。

解题

这道题挺简单的，就是一开始没看清楚题目导致卡了一下。一定要注意审题！ 首先写出生成两个辅助列

select
    player_id,
    event_date,
    lead(event_date, 1) over(partition by player_id order by event_date asc) as lead_date,
    row_number() over(partition by player_id order by event_date asc) as rn
from Activity
;

然后直接基于上述临时表进行计算即可

with tmp as (select
    player_id,
    event_date,
    lead(event_date, 1) over(partition by player_id order by event_date asc) as lead_date,
    row_number() over(partition by player_id order by event_date asc) as rn
from Activity)

select round(tba.pid_cnt/tbb.num, 2) as fraction from
    (select
        count(distinct player_id) as pid_cnt
    from tmp
    where rn=1 and datediff(lead_date, event_date)=1) as tba,
    (select count(distinct player_id) as num from Activity) as tbb
;