After you finished hacking the plane, your laptop dies: you forgot to charge it! Rookie mistake, or the calculations do consume a lot of power. The plane has a power outlet, but somehow it’s a nonstandard one, you need an adapter. But first a cocktail! Then you empty your backback on the seat next to you and find out that you have dozens of different adapters, which you can stack together. None of them really work, and hopefully you don’t blow another fuse, or set the plane on fire!
The adapters you have can transform an imaginary Jolts value to another Jolts value, up to 3 Jolts up. And you find out that your laptop charger jumps 3 Jolts more than the highest rated adapter you have, although right now you don’t know either of these value … And since your laptop died, you need to calculate this on paper. Here is the list of 90 adapters in your backpack. Good luck!
Task 1: If you plug all 90 adapters together (Hopefully the plane is long enough, and the flight attendant does not spot what you are doing), what is the distribution of Joltage differences between the outlet (with 0 Jolts  seriously, no juice to begin with?), all adapters, and your +3 Jolts charger? Find a set that uses all adapters, calculate the Joltage differences, and multiply the +1 with the +3 differences. Seriously?
Task 2: Someone things that 90 adapters are not enough. Figure out in how many ways the 90 adapters can be arranged. On paper, because your laptop is still dead.
Preparation
Not much, just load the data. I changed the BIGINT
to INT; sufficient for today.


And I’m curious:


90 adapters. Seriously? With a Jolt spread between 1 and 140. Again, hopefully this plane will not go up in flames.
Task 1
Need to calculate the differences first. A Windowing Function comes handy, and then wrapped into a CTE:


This produces the differences:


Based on the difference I have to find the ones which match the task:


And here is my result:


Task 2
The query for this is basically a three times self join, calculating all possibilities and return them. In the end select the max value from the list, because that is the highest number of possibilities.


The result is an insane high number:


But I still have no plan why I need to know in how many ways I can connect way too many adapters to a plane. My head is swirling now.