Sooner or later in most high school statistics courses students are presented with following problem; “How many people need to be in a room for there to be a 50% chance of 2 people having the same birthday?” This problem, known as the birthday paradox (which we wrote about in 2011), serves as an introduction into basic combinatorics and is appealing because the solution is surprising.
Here’s a very easy way to calculate the answer in SQL, using the XLeratorDB PERMUT function.
DECLARE @x as int = 2
DECLARE @p as float = 1-wct.PERMUT(365,@x)*wct.POWER(365,-@x)
WHILE @p < 0.5
BEGIN
SET @x = @x + 1
SET @p = 1-wct.PERMUT(365,@x)*wct.POWER(365,-@x)
END
SELECT @x as N
This produces the following result.
N
-----------
23
3 people having the same birthday
Using McKinney’s formulation, the probability of three people having the same birthday in a room of size n can be defined as:
The term P(n;n1,n2 ) is the probability that no birthday is shared k or more times in a specific arrangement of n people. The real challenge, then, is coming up with all the possible arrangements of n people, something that is actually quite simple to do in SQL. Let’s look at an example.
In a room with 10 people, we are looking for all the combinations of n1 + n2 = 10, where n1 is the number of non-repeated items and n2 is the number or pairs. With such a small number, it’s easy to see all the possible arrangements.
