queries

//Query to discover 30 day rolling retention//

SELECT
 joined_day,
  COUNT (DISTINCT player_id ) total_players, -- count all unique player_id's to get total
  SUM(retained) / COUNT(DISTINCT player_id) AS retention_rate,
 
FROM (
  SELECT -- to identify each players join date, last game so we can identify n retained and calc. retention rate
    player_id,
    last_game,
    joined_day,
    outcome,
    last_game-joined_day AS dif, --the amount of days between a player joining and theit last game played
    CASE
      WHEN last_game-joined_day <=30 THEN 1 -- assigning a numerical value for all the players who were still engaged on day 30 
    ELSE -- assigning a numerical value for all players who were not still engaged 30 days in
    0
  END
    AS retained -- shows the number of retained players at 30 days
  FROM (
    SELECT
      a.player_id,
      MAX(b.day) last_game,
      joined AS joined_day,
      b.outcome
    FROM
      heroic-district-329518.gamecompanydata.player_info AS a
    JOIN
      heroic-district-329518.gamecompanydata.matches_info b
    ON
      a.player_id=b.player_id
    GROUP BY
      player_id,
      b.outcome,
      joined_day,
      b.outcome
    )) -- end of nesting
GROUP BY 
joined_day


//query to discover all pertainent player info//

SELECT
  DISTINCT pi.player_id,
  location,
  age AS age_at_signup,
  system,
  pi.joined,
  m.outcome,
  MAX(m.day) AS last_match,
  COUNT(pu.item_id) AS purchases,
  ii.price
FROM
  `heroic-district-329518.gamecompanydata.player_info` AS pi 
INNER JOIN
  `heroic-district-329518.gamecompanydata.matches_info` m -- join 1
ON
  Pi.player_id = m.player_id
INNER JOIN
  `heroic-district-329518.gamecompanydata.purchase_info` pu --join 2
ON
  Pi.player_id = pu.player_id
INNER JOIN
  `heroic-district-329518.gamecompanydata.item_info` ii -- join 3
ON
  pu.item_id = ii.item_id
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  9
ORDER BY
  pi.joined
LIMIT
  1000