Can you get the Data for COOP Campaigns

[Itsgotime45]

Would be interesting to see DATA on the coop side of things for FAF on max difficulty. DATA sets I would like to see too: -How many attempts per mission with win to loss ratio based on skill level. (If possible) -Most played missions with skill level. (If possible) -Time to completion with a win with skill level. (If possible) -Missions with most failures and the one with the most wins. (If possible) -APM used -Reclaim -Defensive structures built -Game enders such as nukes, experimentals, and Artillery built. (If possible) -RAS Support Commanders built. (If possible) -Mass overflow over time to see how mass is used (If possible) -Units lost with Units destroyed Ratio per skill level.

I can not remember anything else on the top of my head atm but it would be cool to see a lot data sets to see how players behave in a Coop setting. Its something that I see the least DATA on.

[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.