LeetCode MySQL 1097. 游戏玩法分析 V

文章目录

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           |
+------------+----------+----------------+
玩家 132016-03-01 安装了游戏,
但只有玩家 12016-03-02 重新登录,
所以 2016-03-01 的第一天留存时间是 1/2=0.50

玩家 22017-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

我的CSDN博客地址 https://michael.blog.csdn.net/

长按或扫码关注我的公众号(Michael阿明),一起加油、一起学习进步!
Michael阿明

©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页