Calculating odds of M&M pulls using PLSQL

Standard

There was a question/challenge posted to a bunch of us nerds, and I decided to go overkill by brute force calculations. The challenge came down to what would the odds be in a 6 M&M pull, of getting one of each color? I wrote a SQL script (PLSQL) that uses the observed color distribution found here and came up with my answer: 1.43267503022458011648%

98.55% of the time you’re not going to get one of each color, or of all the same color.

Also interesting to note is the odds of getting all of each of the colors:

BLUE:       0.0038303288464389083136%
BROWN:  0.0008060825508063215616%
GREEN:    0.0039315653631577133056%
ORANGE: 0.0080050508509564440576%
RED:          0.0008198418170944%
YELLOW:  0.0007791407675257913344%

Code:

WITH COLOR_DIST AS (
SELECT .1836 AS ODDS, 'BLUE' AS COLOR FROM DUAL
UNION SELECT .1416 AS ODDS, 'BROWN' AS COLOR FROM DUAL
UNION SELECT .1844 AS ODDS, 'GREEN' AS COLOR FROM DUAL
UNION SELECT .2076 AS ODDS, 'ORANGE' AS COLOR FROM DUAL
UNION SELECT .1420 AS ODDS, 'RED' AS COLOR FROM DUAL
UNION SELECT .1408 AS ODDS, 'YELLOW' AS COLOR FROM DUAL)
SELECT COLOR_MATCH_TYPES
 ,SUM(ODDS) * 100 AS ODDS
 ,'1:' || ROUND(1 / SUM(ODDS), 0) AS ONE_IN_WHAT
FROM (
 SELECT CASE 
 WHEN ODDS_1.COLOR = ODDS_2.COLOR
 AND ODDS_1.COLOR = ODDS_3.COLOR
 AND ODDS_1.COLOR = ODDS_4.COLOR
 AND ODDS_1.COLOR = ODDS_5.COLOR
 AND ODDS_1.COLOR = ODDS_6.COLOR
 THEN 'SAME COLOR ' || ODDS_1.COLOR
 WHEN ODDS_1.COLOR NOT IN (
 ODDS_2.COLOR
 ,ODDS_3.COLOR
 ,ODDS_4.COLOR
 ,ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_2.COLOR NOT IN (
 ODDS_3.COLOR
 ,ODDS_4.COLOR
 ,ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_3.COLOR NOT IN (
 ODDS_4.COLOR
 ,ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_4.COLOR NOT IN (
 ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_5.COLOR NOT IN (ODDS_6.COLOR)
 THEN 'DIFFERENT COLORS'
 ELSE 'OTHER DISTRIBUTION'
 END AS COLOR_MATCH_TYPES
 ,ODDS_1.ODDS * ODDS_2.ODDS * ODDS_3.ODDS * ODDS_4.ODDS * ODDS_5.ODDS * ODDS_6.ODDS AS ODDS
 FROM COLOR_DIST ODDS_1
 CROSS JOIN COLOR_DIST ODDS_2
 CROSS JOIN COLOR_DIST ODDS_3
 CROSS JOIN COLOR_DIST ODDS_4
 CROSS JOIN COLOR_DIST ODDS_5
 CROSS JOIN COLOR_DIST ODDS_6
 )
GROUP BY COLOR_MATCH_TYPES
ORDER BY 1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s