Complex query to count votes with a redistribution system - follow-up

Question

I've previously posted a question about simplifying a query to count votes with a distribution system. One of the contributors, @200_success, pointed out that it calculated the surplus redistribution based on the second favored candidates original votes rather than their second rank on the winners ballots.

How do I simplify it in the best possible way? This is the resulting table with its queries:

 +-----------+-------+---------------+----------------+----------------------------+
 | CANDIDATE | VOTES | RANKED_CHOICE | REDISTRIBUTION | VOTES_AFTER_REDISTRIBUTION |
 +-----------+-------+---------------+----------------+----------------------------+
 |         1 |     8 |             0 |             -1 |                          7 |
 |         2 |     1 |             4 |            0.5 |                        1.5 |
 |         3 |     2 |             1 |          0.125 |                      2.125 |
 |         4 |     4 |             2 |           0.25 |                       4.25 |
 |         5 |     2 |             1 |          0.125 |                      2.125 |
 |         6 |     3 |             0 |              0 |                          3 |
 +-----------+-------+---------------+----------------+----------------------------+

SELECT vote_candidate candidate, original_votes votes, ranked_choice, surplus_redistribution redistribution, (original_votes + surplus_redistribution) votes_after_redistribution
FROM (
  SELECT c.vote_candidate, (
    SELECT (
      (MAX(votes_above_the_threshold) - (
        SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
        FROM votes
      )) / MAX(votes_above_the_threshold)
    ) ratio
    FROM (
      SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
      FROM vote_orders
      WHERE vote_order = 1
      GROUP BY vote_candidate
      HAVING votes_above_the_threshold >= (
        SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
        FROM votes
      )
    ) t
    WHERE votes_above_the_threshold = (
      SELECT MAX(votes_above_the_threshold)
      FROM vote_orders
    )
  ) surplus_ratio, c.original_votes, '0' ranked_choice, LEAST(0,t.threshold - c.original_votes) surplus_redistribution
  FROM (
    SELECT o.vote_candidate, COUNT(*) original_votes
    FROM vote_orders o
    WHERE o.vote_order = 1
    GROUP BY o.vote_candidate
  ) c
  CROSS JOIN (
    SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) AS threshold
    FROM votes
  ) t
  GROUP BY c.vote_candidate
  UNION ALL
  SELECT vote_candidate, (
    SELECT (
      (
        SELECT (
          (MAX(votes_above_the_threshold) - (
            SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
            FROM votes
          )) / MAX(votes_above_the_threshold)
        ) ratio
        FROM (
          SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
          FROM vote_orders
          WHERE vote_order = 1
          GROUP BY vote_candidate
          HAVING votes_above_the_threshold >= (
            SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
            FROM votes
          )
        ) t
        WHERE votes_above_the_threshold = (
          SELECT MAX(votes_above_the_threshold)
          FROM vote_orders
        )
      )
    ) ratio
  ) surplus_ratio, d.original_votes, COUNT(*) ranked_choice, (
    ROUND((COUNT(*) * (
      SELECT (
        (
          SELECT (
            (MAX(votes_above_the_threshold) - (
              SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
              FROM votes
            )) / MAX(votes_above_the_threshold)
          ) ratio
          FROM (
            SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
            FROM vote_orders
            WHERE vote_order = 1
            GROUP BY vote_candidate
            HAVING votes_above_the_threshold >= (
              SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
              FROM votes
            )
          ) t
          WHERE votes_above_the_threshold = (
            SELECT MAX(votes_above_the_threshold)
            FROM vote_orders
          )
        )
      ) ratio
      FROM (
        SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        FROM vote_orders a
        INNER JOIN
        (
          SELECT vote_id, MIN(vote_order) AS min_vote_order
          FROM vote_orders
          WHERE vote_candidate NOT IN ((
            SELECT vote_candidate
            FROM (
              SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
              FROM vote_orders
              WHERE vote_order = 1
              GROUP BY vote_candidate
              HAVING (
                votes_above_the_threshold >= (
                  SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
                  FROM votes
                )
                OR (
                  votes_above_the_threshold >= (
                    SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
                    FROM votes
                  )
                  AND votes_above_the_threshold = 0
                )
                OR (
                  votes_above_the_threshold = 0
                )
              )
            ) t
          ))
          GROUP BY vote_id
        ) b
        ON a.vote_id = b.vote_id
        AND a.vote_order = b.min_vote_order
        INNER JOIN
        (
          SELECT vote_id
          FROM vote_orders
          WHERE vote_candidate = (
            SELECT vote_candidate
            FROM (
              SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
              FROM vote_orders
              WHERE vote_order = 1
              GROUP BY vote_candidate
              HAVING votes_above_the_threshold >= (
                SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
                FROM votes
              )
            ) t
            WHERE votes_above_the_threshold >= (
              SELECT MAX(votes_above_the_threshold)
              FROM vote_orders
            )
          )
          AND vote_order = 1
        ) c
        ON a.vote_id = c.vote_id
        GROUP BY vote_candidate
      ) t
      WHERE votes_above_the_threshold >= (
        SELECT MAX(votes_above_the_threshold)
        FROM vote_orders
      )
      LIMIT 1
    )), 3)
  ) surplus_redistribution 
  FROM vote_orders a
  INNER JOIN
  (
    SELECT vote_id, MIN(vote_order) AS min_vote_order
    FROM vote_orders
    WHERE vote_candidate NOT IN ((
      SELECT vote_candidate
      FROM (
        SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
        HAVING (
          votes_above_the_threshold >= (
            SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
            FROM votes
          )
          OR (
            votes_above_the_threshold >= (
              SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
              FROM votes
            )
            AND votes_above_the_threshold = 0
          )
          OR (
            votes_above_the_threshold = 0
          )
        )
      ) t
    ))
    GROUP BY vote_id
  ) b
  ON a.vote_id = b.vote_id
  AND a.vote_order = b.min_vote_order
  INNER JOIN
  (
    SELECT vote_id
    FROM vote_orders
    WHERE vote_candidate = 1
    AND vote_order = 1
  ) c
  ON a.vote_id = c.vote_id
  LEFT OUTER JOIN
  (
    SELECT vote_candidate o, COUNT(*) AS original_votes
    FROM vote_orders 
    WHERE vote_order = 1
    GROUP BY vote_candidate
  ) d
  ON a.vote_candidate = d.o
  GROUP BY vote_candidate
  ORDER BY surplus_redistribution DESC
) y
GROUP BY vote_candidate;

Table schema:

CREATE TABLE votes
(
  vote_id INT NOT NULL AUTO_INCREMENT,
  vote_candidate_a INT,
  vote_candidate_b INT,
  vote_candidate_c INT,
  vote_candidate_d INT,
  vote_candidate_e INT,
  vote_candidate_f INT,
  PRIMARY KEY vote_id(vote_id)
);

INSERT INTO votes
VALUES
(NULL, 1, 3, 2, 5, 4, 6),
(NULL, 1, 2, 4, 6, 3, 5),
(NULL, 5, 3, 2, 1, 4, 6),
(NULL, 6, 1, 5, 3, 4, 2),
(NULL, 2, 3, 5, 6, 1, 4),
(NULL, 4, 1, 6, 3, 2, 5),
(NULL, 3, 2, 6, 1, 5, 4),
(NULL, 4, 3, 1, 6, 2, 5),
(NULL, 1, 2, 4, 3, 6, 5),
(NULL, 1, 5, 3, 2, 4, 6),
(NULL, 4, 5, 6, 2, 3, 1),
(NULL, 1, 4, 2, 3, 5, 6),
(NULL, 1, 2, 3, 4, 5, 6),
(NULL, 3, 6, 5, 1, 4, 2),
(NULL, 1, 2, 3, 4, 5, 6),
(NULL, 6, 5, 4, 3, 2, 1),
(NULL, 4, 3, 1, 5, 6, 2),
(NULL, 6, 3, 1, 2, 5, 4),
(NULL, 1, 4, 6, 3, 2, 5),
(NULL, 5, 3, 6, 4, 2, 1);

CREATE TABLE vote_orders
(
  id INT NOT NULL AUTO_INCREMENT,
  vote_id INT,
  vote_order INT,
  vote_candidate INT,
  PRIMARY KEY id(id)
);

INSERT INTO vote_orders (id, vote_id, vote_order, vote_candidate)
SELECT NULL, vote_id, 1, vote_candidate_a FROM votes
UNION
SELECT NULL, vote_id, 2, vote_candidate_b FROM votes
UNION
SELECT NULL, vote_id, 3, vote_candidate_c FROM votes
UNION
SELECT NULL, vote_id, 4, vote_candidate_d FROM votes
UNION
SELECT NULL, vote_id, 5, vote_candidate_e FROM votes
UNION
SELECT NULL, vote_id, 6, vote_candidate_f FROM votes;

Answer 1

It looks better, but I'm still going to recommend using a variable to replace this snippet.

SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes

You could perhaps write a stored procedure to contain the logic as well. While it could also be stored in your schema as a view, it seems a little silly to have a view that returns one record with one field.

This one should also be a stored proedure that returns a single value.

 SELECT (
      (MAX(votes_above_the_threshold) - (
        SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
        FROM votes
      )) / MAX(votes_above_the_threshold)
    ) ratio

Execute your stored procedure once, and store the result in a variable. Try to reduce the layers of nesting. You anytime you reach for cntl+c, stop and consider how you could reuse that particular piece of code.

Answer 2

One big problem I see with your DDL is that your candidate are multiple columns in votes, rather than being a foreign key to a candidates table. This of itself would make your structure much better and easier to work with.

But the elephant in the room is obvious: SUBQUERIES! Way too many of them!

Another thing, please use -- Comments or /* Comments */ to make your code easier to understand for the next guy who is going to review it. As it is right now, it looks like a wall of SQL code with no rhyme or reason to it.

Listen I would love to review this code, and I have tried, spent several hours in a word editor highlighting different sections, but it is so incredibly convoluted. My suggestion is to start from the beginning but this time bearing your expected results in mind, working from there and fixing syntax and other errors as they come along. Use variables whenever possible for a repeated operation. Best of luck to you.