【SQL】连续登录

目录

题目

分析

代码

---

题目

Table: Activity

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

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

结果格式如下所示：

示例 1：

输入：
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

分析

报告在首次登录的第二天再次登录的玩家的 比率

几个keywords，首次登录，翌日再次登录，玩家比率

本质上是一个连续登录，含首次连续两天，

首次登录

针对同一palyer_id， group by player_id

通过min函数即可实现，min(event_date)

含首次连续两天

通过date_add(date,INTERVAL expr type)函数向日期添加指定的时间间隔

这里先获取首次登录日期增加一天的日期，date_add(min(event_date), interval 1 day)

然后通过where in将增加一天的日期与表中登录日期比对，获取存在的第二天的日期

where (player_id, event_date) in

(select player_id, date_add(min(event_date), interval 1 day) from Activity group by player_id)

计算比率，四舍五入到小数点后两位。

round(count(*)/(select count(distinct player_id) from Activity), 2)

代码

select round(count(*)/(select count(distinct player_id) from Activity), 2) fraction
from Activity
where (player_id, event_date) in 
    (select player_id, date_add(min(event_date), interval 1 day)
    from Activity 
    group by player_id)