Can you get the Data for COOP Campaigns

[yaniv-aknin]

Can you get the Data for COOP Campaigns

[yaniv-aknin]

Heya, thanks for this idea!

Here's what I got to so far:

SELECT
  g.id game_id,
  g.startTime game_start_time,
  ROUND(TIMESTAMP_DIFF(g.endTime, g.startTime, SECOND)/60, 1) game_duration_minutes,
  g.replayUrl game_replay_url,
  ARRAY_LENGTH(g.playerStats_gamePlayerStats) game_total_players,
  validity game_validity,

  mapVersion_mapVersion.map_map.displayName map_name,
  faf.GEOMETRY_NAME(mapVersion_mapVersion.width, mapVersion_mapVersion.height) map_geometry,
  mapVersion_mapVersion.thumbnailUrlLarge map_thumbnail,

  ps.result player_result,
  faf.FACTION_NAME(ps.faction) player_faction,
  ps.player_player.id player_id,
  ps.player_player.login player_login,
  ROUND(ps.beforeMean - 3 * ps.beforeDeviation, 1) player_before_rating,
  ROUND(ps.afterMean - 3 * ps.afterDeviation, 1) player_after_rating,

FROM `fafalytics.faf.games_denorm` g
CROSS JOIN UNNEST(playerStats_gamePlayerStats) ps
WHERE featuredMod_featuredMod.displayName = "Coop"

This yields this data, which is quite surprising. Not that many games, and for example the top rated coop game is missing (!).

That's good, because you probably helped me find a problem in how I scraped data from FAF servers, but it will take me some time to look into what did I screw up and fix it. :)

[yaniv-aknin]

OK, I found the problem; the games_denorm view was using an INNER JOIN on the map, and apparently many coop games don't have a map associated with them (for reasons I still don't understand; but you can see that the top rated game from before has null for a mapversion relationship).

Once I switched to using LEFT JOIN, I found ~1.3M games with the above query, which makes a lot more sense. I'll let you know when might I finish the associated analyses (processing replays for 1.3M games might be a challenge).

[Itsgotime45]

I appreciate the work your doing. I will be waiting for the analyses. Thanks again.