demo1.sql

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS games;

CREATE TABLE users (
  id         serial PRIMARY KEY,
  uname      text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT NOW()
);

CREATE TABLE games (
  id         serial PRIMARY KEY,
  map_name   text NOT NULL,
  winner_ids int[] NOT NULL DEFAULT array[]::int[],
  loser_ids  int[] NOT NULL DEFAULT array[]::int[],
  created_at timestamp with time zone NOT NULL DEFAULT NOW()
);


INSERT INTO users (id, uname)
  VALUES (1, 'blockdude'), (2, 'koolkate'),
         (3, 'micro_johnston'), (4, 'miss_priss');

INSERT INTO games (map_name, winner_ids, loser_ids)
VALUES
('power_zone',       Array[1, 3], Array[2, 4]),
('technology_plant', Array[1, 4], Array[2, 3]),
('the_parish',       Array[1, 3], Array[4, 2]),
('the_longest_yard', Array[4, 2], Array[1, 3]),
('sandtrap',         Array[1, 4], Array[2, 3]),
('battle_course_4',  Array[1, 3], Array[4, 2]),
('facility',         Array[3, 1], Array[4, 2]),
('tokays_tower',     Array[3, 2], Array[1, 4]),
('twisted_meadows',  Array[3, 1], Array[2, 4]),
('blood_gultch',     Array[2, 4], Array[1, 3]),
('de_dust',          Array[1, 4], Array[3, 2]);

-- Sandbox

-- blockdude's W:L ratio
-- Result: https://dl.dropboxusercontent.com/spa/quq37nq1583x0lf/i44p1-yg.png
SELECT
 games_won.n  wins,
 games_lost.n losses,
 games_won.n::float / games_lost.n::float ratio
FROM
  (SELECT count(*) n FROM games WHERE 1 = ANY (winner_ids)) games_won, 
  (SELECT count(*) n FROM games WHERE 1 = ANY (loser_ids)) games_lost
;

-- Calculate win probability
-- Result: https://dl.dropboxusercontent.com/spa/quq37nq1583x0lf/ur-8399o.png
select 
  winners.id winner_id, 
  losers.id loser_id, 
  games_won.n win_count,
  games_lost.n loss_count,
  games_won.n::float / (games_won.n + games_lost.n) win_chance
from (
  select distinct unnest(winner_ids || loser_ids) id
  from games
) winners, (
  select distinct unnest(winner_ids || loser_ids) id
  from games
) losers, lateral (
  select count(games.*) n
  from games
  where 
    winners.id = ANY (games.winner_ids)
    and losers.id = ANY (games.loser_ids)
) games_won, lateral (
  select count(games.*) n
  from games
  where 
    losers.id = ANY (games.winner_ids)
    and winners.id = ANY (games.loser_ids)
) games_lost
where
  winners.id != losers.id  -- Users can't play against themselves
  and (games_won.n + games_lost.n) > 0 -- there were games played between the players