Holiday Special: Santa’s SQL Penance

I was watching the holiday classic Frosty the Snowman with my daughter, and this caught my attention. At the very end, the magician’s penance handed out by Santa was to write the sentence “I am very sorry for what I did to Frosty” 100 gazillion times. My daughter seemed content probably thinking that the bad guy learned his lesson and was on his way to becoming reformed. But in my head, alarm bells were ringing with the thought, “this guy has no idea what he is in for.”

Frosty SQL
Credits - Frosty the Snowman

Though the number 100 Gazillion does not technically exist, we can still extrapolate that it is a rather large number. Most children watching the movie will be aware of the numbers one billion and one trillion so one gazillion is likely more than each of them. Let’s assume that gazillion should be replaced with quadrillion (probably should be quintillion since it is a ga-zillion which would be greater than zillion but we’ll later see why a quadrillion should be sufficient). Working with the number 100 quadrillion, let’s start see just how long of a sentence this guy must serve in order to receive a Christmas present from Santa again.

I tried writing the sentence very quickly and sloppily and it took 5 seconds to write it in full. Some simple math shows that it takes a little less than 32 years to simply live one billion seconds.

select 1000000000.0/60.0/60/24/365.25 as [Years to Live One Billion Seconds]

Frosty Paper

To write this sentence 100 quadrillion times straight up would take 15,844,043,907.0145 years clearly longer than anybody’s lifetime.

select (100000000000000000.0*5)/60.0/60/24/365.25 as [Years to Write One Hundred Quadrillion Sentences]

Frosty Paper

But the antagonist has one thing in his advantage, he is a magician (ignore the fact that he does not have the hat anymore, just pretend the poor guy can still do some magic).

What if he can write a sentence and in that process enchant the pen to keep writing the sentence. Then he repeats this process over and over. For simplicity’s sake, assuming an infinite supply of pens and paper and pens do not run out of ink, this process would exponentially decrease the time it would take to reach 100 quadrillion sentences. A simple loop can be used to show how long this would take.

Frosty

–Simulate process of writing a sentence which animates the pen to repeat writing that sentence
–then repeating this process until number of desired sentences is achieved

declare @tblPaper table(newPen bigint, animatedPens bigint, totalSentences bigint)

–Insert first pen writing a sentence which will become animated and repeat writing that sentence
insert into @tblPaper(newPen, animatedPens, totalSentences)
select 1 as [Sentence from New Pen]
,(select isnull(sum(newPen),0) from @tblPaper) as [Sentences from Previously Animated Pens]
,(select 1+isnull(sum(newPen),0)+isnull(max(totalSentences),0) from @tblPaper) [Total Number of Sentences at End of Time Unit]

–Keep writing sentences and animating pens until desired number of sentences is written
while ((select max(totalSentences) from @tblPaper)< 100000000000000000)
begin
–write another sentence and animate another pen
insert into @tblPaper(newPen, animatedPens, totalSentences)
select 1 as [Sentence from New Pen]
,(select isnull(sum(newPen),0) from @tblPaper) as [Sentences from Previously Animated Pens]
,(select 1+isnull(sum(newPen),0)+isnull(max(totalSentences),0) from @tblPaper) [Total Number of Sentences at End of Time Unit]
end

–Show the totals for each 5 second time unit
select newPen as [Sentence from New Pen]
,animatedPens as [Sentences from Previously Animated Pens]
,totalSentences as [Total Number of Sentences at End of Time Unit]
,rowNum as [timeUnit-5seconds]
from (select *,row_number() over(order by totalSentences) as rowNum from @tblPaper) a

Frosty Paper 3

If you have not already guessed, this loop simply will not do. When I impatiently hit stop after only 5 minutes the result set displayed after 5 minutes showed an interesting pattern. I noticed that the total number of sentences was the sum of current and all of the previous time units (if 5 time units then 15 sentences = [5+4+3+2+1] time units). Since it only takes five seconds to write a sentence with a pen, we now know it only would take 25 seconds (5 time units * 5 seconds) to the above magical process to write 15 sentences. So if we build out the equation and apply a little plug and chug we can find the minimum amount of time it would take to use this magical process to write 100 quadrillion sentences. With the function below we can start applying time units to zero in on how much time it would take to write all of the sentences.

create function dbo.GetNumSentences(@timeUnit bigint)
returns bigint
as
begin
declare @retVal bigint
select @retVal=(@timeUnit+1) * (@timeUnit/2) + ((@timeUnit%2) * (@timeUnit/2+1))
return @retVal
end;

The function dbo.GetNumSentences works by applying the idea that if you need to add the sequential numbers 1 to n then you can instead start by adding the first and last numbers of the set. You will notice that (1+n) = (2+(n-1)) = (3+(n-2)) and so on until you reach n/2. At this point if n is odd then you add (n+1)/2 and if n is even you add 0, when this number is added to all previous results you will get the total number of sequential numbers added together. To demonstrate this concept take the example from above:v 15 = [5+4+3+2+1] = [(5+1) + (4+2) + 3] = [6 + 6 + 3] = [2(6)+3]

To further demonstrate, where n is an even number in this case let n=8 36=[8+7+6+5+4+3+2+1] = [(8+1)+(7+2)+(6+3)+(5+4)] = [9+9+9+9] = [4(9)]. Now let’s execute this function to find how many time units are required to arrive at our 100 quadrillion sentences.

Note: in the code below I built out the base equation followed by a method to allow a range of numbers to be attempted at once to speed up zeroing in on the final number of time units.

declare @tbl table (num int)

insert into @tbl select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10

insert into @tbl(num)
select rownum
from (
select row_number() over(order by t1.num) rownum
from @tbl t1
cross join @tbl t2
cross join @tbl t3
cross join @tbl t4
cross join @tbl t5
cross join @tbl t6
) a
where rownum>10

–100,000,000,000,018,810
–100,000,000,000,000,000
declare @timeUnits bigint
select @timeUnits=447213590
select *, dbo.GetNumSentences(a.num+@timeUnits) as [Number of Sentences]
,((a.num+@timeUnits)*5.0)/60.0/60/24/365.25 as [Years to Write Sentences]
from @tbl a
order by a.num

Frosty Paper

So we now see that if the magician never slept, ate, or took any breaks he can use magic to finish writing those sentences in 70.857 years.

Takeaways

One major takeaway I had working with this idea is a healthy respect for the power of exponential changes. Cutting processing time from well over 15 billion years to a little more than 70 years shows just how powerful compounding changes affect growth. My other big takeaway was that Santa’s punishments are rather cruel and it would probably be best to not cross him.

Header_4