This month we have a very interesting topic from Matthew McGiffen. He gets back to the roots of the party with code by asking a question on puzzles. It’s a good question, and one that makes me think. I’m not a big puzzler, but I think I’ve solved a few.
Interesting Challenges
One of the things we’ve tried to do at SQLServerCental is come up with some ways to inspire you. We have articles, numerous questions to be answered, our Question of the Day, Crosswords, and more. I’d like to do more, but one thing I’ve found is that puzzles take a lot of time.
I have enjoyed some of the puzzles from the Advent of Code and Project Euler, which are good programming exercises. I’ve used Python and PowerShell to solve some of these, mostly to practice skills in building algorithms and implementing them.
Solving a Puzzle
One of the puzzles that I enjoyed solving was from the Advent of Code 2015, Day 2. This was a surface area problem, and one that reminded me of math class in high school. I always did enjoy that subject.
In any case, I solved the issue by loading some data into a table and then digging in with a few CTEs to
Might not be the best solution, but it was one I enjoyed working out:
---- create table--create table Day2_WrappingPresents
--(
-- dimensions varchar(100)
--)
--go
---- load data
--bulk insert Day2_WrappingPresents
-- from 'C:\Users\Steve\Documents\GitHub\AdventofCode\Day 2 - Wrapping\input.txt'
--go
---- check
---- select * from Day2_WrappingPresents
--go
-- break this down to get the dimensions
with cteSplit (d, el, sw, sh)
as
(
select
dimensions
, endlength = charindex('x', dimensions) - 1
, startwidth = charindex('x', substring(dimensions, charindex('x', dimensions),20)) + charindex('x', dimensions)
, startheight = len(dimensions) - charindex('x', reverse(dimensions)) + 2
-- , c1 = charindex('x', dimensions)
-- , c2 = charindex('x', dimensions, charindex('x', dimensions)+1) -
from day2_wrappingpresents d
)
, cteDimensions
as
(select
d
, l = cast(substring(d, 1, el) as int)
, w = cast(substring(d, sw, sh-sw-1) as int)
, h = cast(substring(d, sh, len(d)) as int)
from cteSplit d
)
, cteOrder
as
( select
d
, small = case
when l <= w and l <= h then l
when w <= l and w <= h then w
when h <= l and h <= w then h
end
, middle = case
when (l >= w and l <= h) or (l <= w and l >= h) then l
when (w >= l and w <= h) or (w <= l and w >= h) then w
when (h >= l and h <= w) or (h <= l and h >= w) then h
end
, large = case
when l >= w and l >= h then l
when w >= l and w >= h then w
when h >= l and h >= w then h
end
from cteDimensions
)
, cteFinal
as
(
select
d
, area = (2 * small * middle) +
(2 * small * large) +
(2 * middle * large)
, slack = (small * middle)
from cteOrder
)
select
sum(area + slack)
from cteFinal
-- drop table Day2_WrappingPresents
Voice of the DBA 