Leetcode 550游戏玩法分析

清溪上

已于 2024-09-24 21:46:41 修改

阅读量274

点赞数 4

文章标签：游戏 sql 数据库数据分析 leetcode

于 2024-09-24 21:35:58 首次发布

本文链接：https://blog.csdn.net/Yaoo415/article/details/142500774

版权

1.题目

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

2.数据准备

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');

3.方法一：从连续登录天数切入

—534 ms击败86.77%

思路
- t1: 给每个玩家分配登录顺序（rn）。
- t2: 计算每个玩家的差异日期，用 rn 减去当前的 event_date，计算出连续登录的情况。
- t3: 计算每个玩家在相同的日期差异下的登录次数（ct），找到有两天连续登录的玩家。
- t4: 统计首次登录且在次日（第二天）登录的玩家数。
- t5: 统计总玩家数。
- 最终结果: 计算并输出次日留存率。

WITH t1 AS (
    SELECT 
        player_id,
        event_date,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
    FROM activity
),
t2 AS (
    SELECT 
        player_id
    FROM t1
    WHERE rn = 1 -- 首次登录
),
t3 AS (
    SELECT 
        t1.player_id
    FROM t1
    JOIN t1 AS t1_next 
        ON t1.player_id = t1_next.player_id 
        AND DATEDIFF(t1_next.event_date, t1.event_date) = 1  -- 间隔1天
        AND t1.rn = 1  -- 第一次登录当天
),
t4 AS (
    SELECT COUNT(DISTINCT player_id) AS retention_count FROM t3
),
t5 AS (
    SELECT COUNT(DISTINCT player_id) AS total_players FROM t2
)
SELECT 
    ROUND(t4.retention_count / t5.total_players, 2) AS fraction
FROM t4, t5;

4.方法二：找出与首次登陆日间隔1天的登陆日

—519 ms击败95.81%

计算首次登录的玩家中，第二天再次登录的比例（次日留存率），思路是找出从首次登录到次日仍然活跃的玩家。
上一个方法旨在寻找连续登录的天数，但其实可以直接在 t1 基础上处理登录间隔为 1 天的玩家。
思路
- t1: 给每个玩家的每次登录打上序号 rn。
- t2: 找出首次登录的玩家。
- t3: 找出那些首次登录后 第二天再次登录 的玩家（DATEDIFF = 1）。
- t4: 计算次日留存的玩家数。
- t5: 计算总玩家数。
- 最终结果: 计算次日留存率，并四舍五入保留两位小数。

WITH t1 AS (
    SELECT 
        player_id,
        event_date,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
    FROM activity
),
t2 AS (
    SELECT 
        player_id
    FROM t1
    WHERE rn = 1 -- 首次登录
),
t3 AS (
    SELECT 
        t1.player_id
    FROM t1
    JOIN t1 AS t1_next 
        ON t1.player_id = t1_next.player_id 
        AND DATEDIFF(t1_next.event_date, t1.event_date) = 1  -- 间隔1天
        AND t1.rn = 1  -- 第一次登录当天
),
t4 AS (
    SELECT COUNT(DISTINCT player_id) AS retention_count FROM t3
),
t5 AS (
    SELECT COUNT(DISTINCT player_id) AS total_players FROM t2
)
SELECT 
    ROUND(t4.retention_count / t5.total_players, 2) AS fraction
FROM t4, t5;