SELECT
  p.PlayerName AS Player,
  CASE WHEN (f.AttackerScore > maps.AvgAttScore) THEN 'Att' WHEN (f.DefenderScore > maps.AvgDefScore) THEN 'Def' ELSE 'Mid' END AS Position,
  (avg(f.AttackerScore) * 3.5 + avg(f.DefenderScore) * 1.0 + (avg(ms.Kills) * 1.0)) * (
    (total(ms.Kills)/total(acc.InstagibShots)) * 1.0 *
    (total(ms.Kills)/(total(ms.Kills)+total(ms.Death))) * 1.0
  ) AS Rank,
  avg(ms.Kills / maps.AvgKills),
  avg(ms.Death / maps.AvgDeaths),
  count(*) AS MatchesPlayed
FROM
  (UTPUGS_AccuracyStats AS acc
    INNER JOIN UTPUGS_Player AS p ON (acc.PlayerID = p.PlayerID)
    INNER JOIN UTPUGS_MatchesTable AS m ON (acc.MatchID = m.MatchID)
    INNER JOIN (
      SELECT
        m.MatchID,
        count(*) AS PlayerCount
      FROM UTPUGS_MatchesTable AS m
        INNER JOIN UTPUGS_MatchStats AS ms ON (m.MatchID = ms.MatchID)
      GROUP BY m.MatchID
    ) AS pc ON (acc.MatchID = pc.MatchID)
    INNER JOIN (
      SELECT
        p.PlayerID,
        count(*) AS MatchCount
      FROM UTPUGS_Player AS p
        INNER JOIN UTPUGS_MatchStats AS ms ON (p.PlayerID = ms.PlayerID)
      GROUP BY p.PlayerID
    ) AS mc ON (acc.PlayerID = mc.PlayerID)
    INNER JOIN (
      SELECT
        m.Gamemap AS MapName,
        avg(f.AttackerScore) AS AvgAttScore,
        max(f.AttackerScore) AS MaxAttScore,
        avg(f.DefenderScore) AS AvgDefScore,
        max(f.DefenderScore) AS MaxDefScore,
        avg(ms.Kills) AS AvgKills,
        max(ms.Kills) AS MaxKills,
        avg(ms.Death) AS AvgDeaths,
        max(ms.Death) AS MaxDeaths
      FROM
        (UTPUGS_MatchesTable AS m
          INNER JOIN UTPUGS_FlagStats AS f ON (m.MatchID = f.MatchID)
          INNER JOIN UTPUGS_MatchStats AS ms ON (m.MatchID = ms.MatchID)
        )
      GROUP BY m.Gamemap
    ) AS maps ON (m.Gamemap = maps.MapName)
    INNER JOIN UTPUGS_MatchStats AS ms
      ON (acc.PlayerID = ms.PlayerID) AND (acc.MatchID = ms.MatchID)
    INNER JOIN UTPUGS_FlagStats AS f
      ON (acc.PlayerID = f.PlayerID) AND (acc.MatchID = f.MatchID)
    INNER JOIN UTPUGS_WeaponStats AS w
      ON (acc.PlayerID = w.PlayerID) AND (acc.MatchID = w.MatchID)
  )
WHERE
  (m.Gamemode LIKE 'iCTF_GameMode_%') AND
  (pc.PlayerCount > 7) AND
  (mc.MatchCount >= 4)
GROUP BY Player, Position
ORDER BY 2 DESC;