Skip to content

1097. Game Play Analysis V 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
WITH
  PlayerToInstallDate AS (
    SELECT player_id, MIN(event_date) AS install_dt
    FROM Activity
    GROUP BY 1
  )
SELECT PlayerToInstallDate.install_dt,
  COUNT(*) AS installs,
  ROUND(
    SUM(IF(Activity.event_date, 1, 0)) / COUNT(PlayerToInstallDate.install_dt),
    2
  ) AS Day1_retention
FROM PlayerToInstallDate
LEFT JOIN Activity
  ON (
    PlayerToInstallDate.player_id = Activity.player_id
    AND DATEDIFF(Activity.event_date, PlayerToInstallDate.install_dt) = 1)
GROUP BY 1;