I saw a post in chat a few days ago, of what I thought was an interesting (albeit fairly simple) statistical challenge:
If I shoot once with 50% chance of target A and 50% of target B, and if I hit target A I have 95% chance to shoot again, then how many of target A will I hit on average?
So I broke out the SQL machine and decided to write something like this, but more generic. So for the 50/50 chance I used a coin-flip-like logic, then for the subsequent rolls, I used the analogy of a dice roll, as I feel these two are clear and should be familiar to anyone.
I wrapped this into a stored procedure
sp_ prefix is very commonly used in databases when naming procedures).
I did as much as I could to make it set-based, but some parts, especially the repeated dice rolls, I couldn't think of a good way to do it without a loop.
Execution is still quite fast, 1-2 seconds for 10000 rows and about 5 seconds for 10000 rows. What can I improve on? Do my statistics make sense?
use PhrancisLocal; go if object_id('dbo.usp_CoinFlipAndDiceRolls') is not null drop procedure dbo.usp_CoinFlipAndDiceRolls; go set ansi_nulls on; go set quoted_identifier on; go create procedure dbo.usp_CoinFlipAndDiceRolls @numberOfRuns int = 1000, @chanceToWinDiceRoll float = null, @includeStatistics bit = 0 as begin set nocount on; /* Default dice roll to 1 chance in 6 if not passed in to the procedure as a parameter: */ if @chanceToWinDiceRoll is null set @chanceToWinDiceRoll = (1.0/6); /* CONSTANTS */ declare @CHANCE_TO_WIN_COIN_FLIP float = 0.5, @WIN bit = 1, @LOSS bit = 0; /* Temporary table which will be used to hold the results of the operations and aggregate statistics from the results to return to the caller. */ if object_id('tempdb..#CoinFlipAndDiceRolls') is not null drop table #CoinFlipAndDiceRolls; create table #CoinFlipAndDiceRolls ( row_id int identity(1,1) primary key, CoinFlipSeed float, CoinFlipWon bit, NumberOfDiceRolls int ); /* Seed the coin flip according to the number of runs desired, and populate the coingFlipWon value accordingly based on a 50% random chance */ declare @row_num int = 1, @CoinFlipSeed float = null; while (@row_num <= @numberOfRuns) begin set @CoinFlipSeed = rand(); insert into #CoinFlipAndDiceRolls ( CoinFlipSeed, CoinFlipWon, NumberOfDiceRolls ) select @CoinFlipSeed, case when @CoinFlipSeed >= @CHANCE_TO_WIN_COIN_FLIP then @WIN else @LOSS end, case when @CoinFlipSeed >= @CHANCE_TO_WIN_COIN_FLIP then 0 else null end; set @row_num = @row_num + 1; end; /* Begin logic for dice rolls: * A dice is rolled once for each run (i.e. row) where the coin flip was a Win. * Each dice roll Win triggers another dice roll, and so on until a dice roll is Lost. * The criteria for a dice roll to be a Win is determined by the @chanceToWinDiceRoll number * where a 0.25 value will result in a 25% chance to Win. * @diceRollSeed represents each individual dice roll. */ declare @diceRollSeed float = null; declare @currentRow int = ( select min(row_id) from #CoinFlipAndDiceRolls where NumberOfDiceRolls = 0 and CoinFlipWon = @WIN ); while exists ( select 1 from #CoinFlipAndDiceRolls where NumberOfDiceRolls = 0 and CoinFlipWon = @WIN ) begin set @diceRollSeed = rand(); while @diceRollSeed <= @chanceToWinDiceRoll begin update #CoinFlipAndDiceRolls set NumberOfDiceRolls = NumberOfDiceRolls + 1 where row_id = @currentRow; /* Roll a new dice value: */ set @diceRollSeed = rand(); end set @currentRow = ( select min(row_id) from #CoinFlipAndDiceRolls where NumberOfDiceRolls = 0 and CoinFlipWon = @WIN ); end; /* Return the result finished result set: */ select * from #CoinFlipAndDiceRolls order by row_id asc; /* Statistics about the finished result set, if the call includes this parameter: */ if @includeStatistics = 1 begin select [NumberOfRuns] = @numberOfRuns, [ChanceToWinDiceRoll] = @chanceToWinDiceRoll, [TotalCoinFlipWins] = sum(convert(int, CoinFlipWon)), [TotalDiceRolls] = sum(coalesce(NumberOfDiceRolls, 0)), [AverageDiceRollsPerCoinFlipWon] = (select avg(NumberOfDiceRolls) from #CoinFlipAndDiceRolls where NumberOfDiceRolls is not null) from #CoinFlipAndDiceRolls; end; /* Cleanup */ if object_id('tempdb..#CoinFlipAndDiceRolls') is not null drop table #CoinFlipAndDiceRolls; end; go
-- No params execute dbo.usp_CoinFlipAndDiceRolls; -- With params execute dbo.usp_CoinFlipAndDiceRolls @numberOfRuns = 1000, @chanceToWinDiceRoll = 0.95; -- With params & statistics execute dbo.usp_CoinFlipAndDiceRolls @numberOfRuns = 1000, @chanceToWinDiceRoll = 0.75, @includeStatistics = 1;
The results look something like this (when ran with