SQL to fetch similar "match" results by percentage

Question

This table stores user votes between user matches. There is always one winner, one loser and the voter.

CREATE TABLE `user_versus` (
  `id_user_versus` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_winner` int(10) unsigned NOT NULL,
  `id_user_loser` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `date_versus` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user_versus`),
  KEY `id_user_winner` (`id_user_winner`,`id_user_loser`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

INSERT INTO `user_versus` (`id_user_versus`, `id_user_winner`, `id_user_loser`, `id_user`, `date_versus`) VALUES
(1, 6, 7, 1, '2013-10-25 23:02:57'),
(2, 6, 8, 1, '2013-10-25 23:02:57'),
(3, 6, 9, 1, '2013-10-25 23:03:04'),
(4, 6, 10, 1, '2013-10-25 23:03:04'),
(5, 6, 11, 1, '2013-10-25 23:03:10'),
(6, 6, 12, 1, '2013-10-25 23:03:10'),
(7, 6, 13, 1, '2013-10-25 23:03:18'),
(8, 6, 14, 1, '2013-10-25 23:03:18'),
(9, 7, 6, 2, '2013-10-26 04:02:57'),
(10, 8, 6, 2, '2013-10-26 04:02:57'),
(11, 9, 8, 2, '2013-10-26 04:03:04'),
(12, 9, 10, 2, '2013-10-26 04:03:04'),
(13, 9, 11, 2, '2013-10-26 04:03:10'),
(14, 9, 12, 2, '2013-10-26 04:03:10'),
(15, 9, 13, 2, '2013-10-26 04:03:18'),
(16, 9, 14, 2, '2013-10-26 04:03:18');

I'm working on a query that fetches similar profiles. A profile is similar, when the voting percentage (wins vs loses) is +/- 10% of the specified profile.

SELECT id_user_winner AS id_user,
    IFNULL(wins, 0) AS wins,
    IFNULL(loses, 0) AS loses,
    IFNULL(wins, 0) + IFNULL(loses, 0) AS total,
    IFNULL(wins, 0) / (IFNULL(wins, 0) + IFNULL(loses, 0)) AS percent
FROM
(
    SELECT id_user_winner AS id_user FROM user_versus 
    UNION
    SELECT id_user_loser FROM user_versus 
) AS u
LEFT JOIN
(
    SELECT id_user_winner, COUNT(*) AS wins
    FROM user_versus
    GROUP BY id_user_winner
) AS w
ON u.id_user = id_user_winner
LEFT JOIN
(
    SELECT id_user_loser, COUNT(*) AS loses
    FROM user_versus
    GROUP BY id_user_loser
) AS l
ON u.id_user = l.id_user_loser

This is the current result:

It's currently returning NULL rows, and they shouldn't be there. What still needs to get optimized (and can't quite put my finger on it) is:

1. bring users similar to user ABC only
2. specify condition that defines who is a similar user to, e.g. user id = 6 (where similar users have +/- 10% difference in percentage with user id 6)

Any help will be appreciated. Thanks!

Answer 1

To calculate wins and losses of each user without having to join the table to itself and use OUTER joins, it is possible to just select wins and losses separately and do a UNION ALL between them, but with additional information if given row represents a win for the user, or a loss.

Then, it's easy to calculate all wins and losses for each user. The tricky part was to incorporate the option for specifying to which user you would like to compare the profiles. I did that with a variable which is set to the value of percentage of the user with given user_id, which you can change from a constant to a variable.

Here is my proposal (comparing to user with id = 6):

SELECT
    player_id AS id_user,
    wins,
    losses,
    wins + losses AS total,
    wins / (wins + losses) AS percent
  FROM (
    SELECT
        player_id,
        SUM(is_a_win) wins,
        SUM(is_a_loss) losses,
        CASE
          WHEN player_id = 6
            THEN @the_user_score := SUM(is_a_win) / (SUM(is_a_win) + SUM(is_a_loss))
          ELSE NULL
         END
      FROM (
        SELECT id_user_winner AS player_id, 1 AS is_a_win, 0 AS is_a_loss FROM user_versus
        UNION ALL SELECT id_user_loser, 0, 1 FROM user_versus
      ) games
    GROUP BY player_id
  ) data
WHERE
  ABS(wins / (wins + losses) - @the_user_score) <= 0.1
;

Output:

ID_USER WINS   LOSSES  TOTAL   PERCENT
6       8       2       10      0.8
9       6       1       7       0.8571

You could of course remove the user whose profile is the base for comparison by adding player_id != 6 (or, in the final solution, some variable name) condition to the outermost WHERE clause.

Example at SQLFiddle: Matching Profiles - Example

Could you provide some feedback if this is what you were looking for, and, if not, what output would you expect?