(player_id, event_date)这种两个字段限制的联合查询第一次见,记录下。注意主键描述。
select player_id, device_id
from activity
where (player_id, event_date) in
(select player_id, min(event_date)
from activity
group by player_id)
编写一个 SQL 查询,报告在首次登录的第二天再次登录的玩家的分数,四舍五入到小数点后两位。换句话说,您需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
SELECT
ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(distinct player_id) FROM Activity),
2) AS fraction
FROM
Activity
WHERE
(player_id,event_date)
IN
(SELECT
player_id,
Date(min(event_date)+1)
FROM Activity
GROUP BY player_id);