find-winners.sql

begin;

CREATE TABLE bets
(
    id integer NOT NULL,
    week integer NOT NULL,
    game1 integer NOT NULL,
    game2 integer NOT NULL,
    game3 integer NOT NULL,
    game4 integer NOT NULL,
    game5 integer NOT NULL,
    game6 integer NOT NULL,
    game7 integer NOT NULL,
    game8 integer NOT NULL,
    game9 integer NOT NULL,
    game10 integer NOT NULL,
    game11 integer NOT NULL,
    game12 integer NOT NULL,
    game13 integer NOT NULL
);

INSERT INTO bets VALUES
  (1, 1, 1,1,1,1,1,1,1,1,1,1,1,1,1),
  (2, 1, 2,1,1,1,1,1,1,1,1,1,1,1,1),
  (3, 1, 3,1,1,1,1,1,1,1,1,1,1,1,1),
  (4, 1, 4,1,1,1,1,1,1,1,1,1,1,1,1),
  (5, 1, 5,1,1,1,1,1,1,1,1,1,1,1,1),
  (6, 1, 6,1,1,1,1,1,1,1,1,1,1,1,1),
  (7, 1, 7,1,1,1,1,1,1,1,1,1,1,1,1),
  (8, 1, 3,6,4,4,4,4,6,6,6,1,5,1,7),
  (9, 1, 2,2,2,2,2,2,2,2,2,2,2,2,2),
  (10, 1, 2,2,1,1,1,1,1,1,1,1,1,1,1);

CREATE TABLE results
(
    week integer NOT NULL,
    game1 integer NOT NULL,
    game2 integer NOT NULL,
    game3 integer NOT NULL,
    game4 integer NOT NULL,
    game5 integer NOT NULL,
    game6 integer NOT NULL,
    game7 integer NOT NULL,
    game8 integer NOT NULL,
    game9 integer NOT NULL,
    game10 integer NOT NULL,
    game11 integer NOT NULL,
    game12 integer NOT NULL,
    game13 integer NOT NULL
);

INSERT INTO results VALUES (1, 1,1,1,1,1,1,1,1,1,1,1,1,1);

SELECT * FROM
  (
    SELECT
    bets.id AS BET_ID,
    SUM(
      CASE results.game1 & bets.game1 WHEN 0 THEN 0 ELSE 1 END +
      CASE results.game2 & bets.game2 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game3 & bets.game3 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game4 & bets.game4 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game5 & bets.game5 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game6 & bets.game6 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game7 & bets.game7 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game8 & bets.game8 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game9 & bets.game9 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game10 & bets.game10 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game11 & bets.game11 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game12 & bets.game12 WHEN 0 THEN 0 ELSE 1 END  +
      CASE results.game12 & bets.game12 WHEN 0 THEN 0 ELSE 1 END
    ) AS CORRECT_GUESSES
    FROM bets
    JOIN results ON (bets.week = results.week)
    WHERE results.week = 1
    GROUP BY bets.id
    ORDER BY bets.id ASC
  ) temp
  WHERE CORRECT_GUESSES >= 11;

rollback;