阿里云sql挑战赛第二题解题思路及答案

准备数据

CREATE TABLE Activity (
player_id INT NOT NULL,
device_id INT NOT NULL,
event_date DATE NOT NULL,
games_played INT NOT NULL,
PRIMARY KEY (player_id, event_date)
);

解题思路

首先要查询一共有多少不重复的游戏id

SELECT COUNT(DISTINCT player_id) FROM activity

去重统计查询

然后再查询一周内至少再有一次游玩的玩家数

SELECT player_id
FROM activity
WHERE event_Date <= DATE_ADD((SELECT MIN(event_Date) FROM activity), INTERVAL 7 DAY)
GROUP BY player_id
HAVING COUNT(*) >= 2  

然后再计算比例

完成代码：

SELECT ROUND(count(*) / (SELECT COUNT(DISTINCT player_id) FROM activity), 9) AS fraction
FROM (
SELECT player_id
FROM activity
WHERE event_Date <= DATE_ADD((SELECT MIN(event_Date) FROM activity), INTERVAL 7 DAY)
GROUP BY player_id
HAVING COUNT(*) >= 2
) AS Subquery;

执行过程中发现小数点保留两位居然结果为0，开始我以为我统计错误呢。