550. Game Play Analysis IV.md

550. Game Play Analysis IV

Question Link

Intuition

If you don't know why where part should contain player_id: because it needs to group by player_id, or it will show just one first login date.

SQL Solution v1

# Write your MySQL query statement below
select round(count(distinct player_id)/(select count(distinct player_id) from Activity), 2) fraction from Activity a
where (player_id, date_sub(event_date, interval 1 day)) in (select player_id, min(event_date) first_login from Activity group by player_id)

SQL Solution v2

select round(count(a.player_id)/(select count(distinct player_id) from Activity),2) as fraction
from Activity a join
(select player_id, min(event_date) as first_day from Activity group by player_id) as t
on a.player_id = t.player_id and a.event_date = date_add(t.first_day, interval 1 day)