LeetCode MySQL 1097. 游戏玩法分析 V
LeetCode MySQL 1097. 游戏玩法分析 V
Michael阿明
发布于 2021-02-19 10:42:37
发布于 2021-02-19 10:42:37
文章被收录于专栏：Michael阿明学习之路
文章目录
1. 题目
Activity 活动记录表
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
（player_id，event_date）是此表的主键
这张表显示了某些游戏的玩家的活动情况
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）我们将玩家的安装日期定义为该玩家的第一个登录日。
我们还将某个日期 X 的第 1 天留存时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。
编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的留存时间。
查询结果格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| 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-01 | 0 |
| 3 | 4 | 2016-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2 | 0.50 |
| 2017-06-25 | 1 | 0.00 |
+------------+----------+----------------+
玩家 1 和 3 在 2016-03-01 安装了游戏，
但只有玩家 1 在 2016-03-02 重新登录，
所以 2016-03-01 的第一天留存时间是 1/2=0.50
玩家 2 在 2017-06-25 安装了游戏，
但在 2017-06-26 没有重新登录，
因此 2017-06-25 的第一天留存时间为 0/1=0.00来源：力扣（LeetCode） 链接：https://leetcode-cn.com/problems/game-play-analysis-v 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
2. 解题
- 先找出安装日期，和安装数量
select distinct install_dt,
count(*) over(partition by install_dt) installs
from
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
) temp{"headers": ["install_dt", "installs"],
"values": [["2016-03-01", 2],
["2017-06-25", 1]]}- 第二天还登录的数量
select a.event_date, count(*) second_login
from Activity a
where(a.player_id, date_sub(a.event_date, interval 1 day))
in
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
)
group by a.event_date{"headers": ["event_date", "second_login"],
"values": [["2016-03-02", 1]]}- 连接后，两指标相除
# Write your MySQL query statement below
with t as
(
select distinct install_dt,
count(*) over(partition by install_dt) installs
from
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
) temp
)
select install_dt, installs, round(ifnull(second_login,0)/installs, 2) Day1_retention
from
t left join
(
select a.event_date, count(*) second_login
from Activity a
where(a.player_id, date_sub(a.event_date, interval 1 day))
in
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
)
group by a.event_date
) t1
on t.install_dt = date_sub(t1.event_date, interval 1 day)
group by install_dt- 评论区简洁答案
select a1.install_dt,
count(*) installs,
round(count(a2.event_date)/count(*),2) Day1_retention
from
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
) a1
left join Activity a2
on a1.player_id = a2.player_id and datediff(a2.event_date, a1.install_dt)=1
group by a1.install_dt本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020/08/01 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
