题目:
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
(player_id,event_date)是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。
编写一个 SQL 查询,报告在首次登录的第二天再次登录的玩家的比率,四舍五入到小数点后两位。换句话说,您需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
查询结果格式如下所示:
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 |
+-----------+-----------+------------+--------------+
Result table:
+-----------+
| fraction |
+-----------+
| 0.33 |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录,所以答案是 1/3 = 0.33
思路:这个是两个比值计算,采用分别计算分母和分子的方法
1.分母计算比较简单,直接计算有多少不同的用户id即可
select count(distinct player_id) from Activity
| count(distinct player_id) |
| ------------------------- |
| 3 |
2.计算分子,找出用户在第一天登录后,第二天继续登录,也就是说求出用户首次登录的后一天,看看是否在这一天也登录
select distinct player_id from Activity
where (player_id,event_date) in
(select player_id ,date(date_add(min(event_date),interval 1 day))
from Activity
group by player_id)
| player_id |
| --------- |
| 1 |
3.将两部分用除法合并,用round()函数设置小数点,利用ifnull函数防止分母为null
select
ifnull(
round(
count(distinct player_id)
/
(select count(distinct player_id) from Activity)
,2
)
,0)as fraction
from Activity
where (player_id,event_date) in
(select player_id ,date(date_add(min(event_date),interval 1 day))
from Activity
group by player_id)