special used vs win %

data.csv

data.sql

SELECT
  MAX(w.name) AS weapon,
  MAX(rule.name) AS rule,
  bp.special,
  COUNT(*) AS battles,
  SUM(CASE WHEN b.is_win = bp.is_my_team THEN 1 ELSE 0 END) AS win,
  MAX(sub.name) AS sub_weapon,
  MAX(sp.name) AS special_weapon
FROM battle2 b
INNER JOIN battle_player2 bp ON b.id = bp.battle_id
INNER JOIN weapon2 w ON bp.weapon_id = w.id
INNER JOIN subweapon2 sub ON w.subweapon_id = sub.id
INNER JOIN special2 sp ON w.special_id = sp.id
INNER JOIN rule2 rule ON b.rule_id = rule.id
WHERE b.is_win IS NOT NULL
AND bp.special IS NOT NULL
GROUP BY w.id, rule.id, bp.special
ORDER BY w.id, rule.id, bp.special

summarize.sql

SELECT
  MAX(rule.name) AS rule,
  bp.special,
  (100.0 * SUM(CASE WHEN b.is_win = bp.is_my_team THEN 1 ELSE 0 END) / COUNT(*))::decimal(5, 2) AS "total win%",
  (100.0 * SUM(CASE WHEN w.key IN ('parashelter', 'heroshelter_replica') AND b.is_win = bp.is_my_team THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN w.key IN ('parashelter', 'heroshelter_replica') THEN 1 ELSE 0 END), 0))::decimal(5, 2) AS "shelter_win%",
  COUNT(*) AS total_btl,
  SUM(CASE WHEN b.is_win = bp.is_my_team THEN 1 ELSE 0 END) AS total_win,
  SUM(CASE WHEN w.key IN ('parashelter', 'heroshelter_replica') THEN 1 ELSE 0 END) AS "shelter_btl",
  SUM(CASE WHEN w.key IN ('parashelter', 'heroshelter_replica') AND b.is_win = bp.is_my_team THEN 1 ELSE 0 END) AS "shelter_win"
FROM battle2 b
INNER JOIN battle_player2 bp ON b.id = bp.battle_id
INNER JOIN weapon2 w ON bp.weapon_id = w.id
INNER JOIN subweapon2 sub ON w.subweapon_id = sub.id
INNER JOIN special2 sp ON w.special_id = sp.id
INNER JOIN rule2 rule ON b.rule_id = rule.id
WHERE b.is_win IS NOT NULL
AND bp.special IS NOT NULL
GROUP BY rule.id, bp.special
ORDER BY rule.id, bp.special

summarized.csv