T-SQL Simulator for Monty Hall Paradox

Introduction

The following T-SQL code simulates one very interesting mathematical paradox called Monty Hall. The Monty Hall problem is a brain teaser, in the form of a probability puzzle, loosely based on the American television game show Let's Make a Deal and named after its original host, Monty Hall. More information about this paradox can be found here.

The Code

The following code can be used to simulate Monty Hall Paradox. Just set @maxGames to number of games you would like to simulate and run the query. It will take 10-20 seconds to run 1000 games.

declare @counter int = 0
declare @maxGames int = 1000
declare @randomPrizeDoor tinyint
declare @randomChoosenDoor tinyint
declare @randomOpenedDoor tinyint

-- Games
declare @games table
(
    GameId int not null identity(1, 1),
    PrizeDoor tinyint not null,
    ChoosenDoor tinyint not null,
    HostOpensDoor tinyint not null,
    ResultIfYouStay as case when PrizeDoor = ChoosenDoor then 1 else 0 end,
    ResultIfYouSwitch as case when PrizeDoor = ChoosenDoor then 0 else 1 end
)

while @counter < @maxGames
begin
    -- Hosts put a prize behind random door 1-3
    SELECT @randomPrizeDoor = ABS(CAST(NEWID() AS binary(6)) %3) + 1 FROM sysobjects

    -- Player randomly selects one door 1-3
    SELECT @randomChoosenDoor = ABS(CAST(NEWID() AS binary(6)) %3) + 1 FROM sysobjects

    -- Host shows one door where there is no prize
    SELECT TOP 1 @randomOpenedDoor = Door
    FROM (select 1 as Door union all select 2 union all select 3) T
    WHERE T.Door not in (@randomPrizeDoor, @randomChoosenDoor)

    insert into @games(PrizeDoor, ChoosenDoor, HostOpensDoor)
    select @randomPrizeDoor, @randomChoosenDoor, @randomOpenedDoor
    set @counter = @counter + 1
end

select 1.0 * sum(ResultIfYouStay) / @maxGames as ChancesToWinIfYouStay, _
	1.0 * sum(ResultIfYouSwitch) / @maxGames as ChancesToWinIfYouSwitch
from @games

Code is intentionally written this way (Row By Agonizing Row) to simulate real life game. I know it could be made much more efficient, but the point here is not speed but to demonstrate paradox.

Points of Interest

It is very interesting how our brain is trained in a certain way and many people, who never heard about this paradox before, would immediately say "It does not matter what door you choose, it's 50-50!". You can then demonstrate your matematical skills, draw few tables and pull other tricks for your pocket to explain why they might be wrong or you can simply run this code. :) 

History

• 30^th November, 2015 - Initial version