SQL每日一题(20210525)报告在首次登录后第二天再次登录的玩家的分数

time丶sand

已于 2022-05-17 20:54:35 修改

阅读量216

点赞数

分类专栏：每日一练文章标签： sql

于 2022-05-17 19:48:18 首次发布

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124798119

版权

每日一练专栏收录该内容

119 篇文章 17 订阅

订阅专栏

该篇博客探讨了如何使用SQL进行复杂的数据分析，特别是针对玩家行为的追踪。通过两个示例查询，展示了如何计算连续两天登录的玩家比例，并对这种行为进行标准化。这些查询涉及到子查询、聚合函数以及日期运算，对于理解和优化大数据分析流程具有实践价值。

摘要由CSDN通过智能技术生成

SQL每日一题(20210830)
在这里插入图片描述

SELECT ROUND((COUNT(*) / (SELECT COUNT(*) FROM (SELECT COUNT(*) FROM T0525 GROUP BY player_id) AS a)), 2) AS FRACTION
FROM T0525 AS T1,
     T0525 AS T2
WHERE T1.player_id = T2.player_id
  AND T1.event_date <
      T2.event_date
  AND DATEDIFF(T1.event_date, T2.event_date) = -1;

在这里插入图片描述

SELECT ROUND(1.0 * (SELECT COUNT(1) AS LX_COUNT
                    FROM (SELECT T1.PLAYER_ID
                          FROM T0525 AS T1,
                               T0525 AS T2
                          WHERE ABS(timestampdiff(day , T1.EVENT_DATE, T2.EVENT_DATE)) = 1
                            AND T1.PLAYER_ID = T2.PLAYER_ID
                          GROUP BY T1.PLAYER_ID
                          HAVING COUNT(1) >
1) AS T) / (SELECT COUNT(1) AS P_COUNT FROM (SELECT PLAYER_ID FROM T0525 GROUP BY PLAYER_ID) AS T),2)