Voice of the DBA

Tag: AdventofCode

2021 Advent of Code–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ll do a post on how to easily get started here as a new blogger, but following the Advent of Code, even some random problems, is a good way to show off some T-SQL skills.

This won’t be a goal for me, but I did start working on the 2021 Advent of Code, taking a few minutes across some days to break from other work and solve a programming problem.

My aim this time is not to get stuck on a problem. If I can’t solve it, or don’t see a way, I leave it and move on. This post has a few thoughts on the first few days.

Day 1

The first problem dealt with loading a set of data and then counting how many times the number increases from the previous number.

Since numbers in a SQL table don’t have a guaranteed order, this was a bit of a hack from me. I created a table and added a clustered index, and then bulk inserted the data. I then moved this in the same order (I hope) to a table with an identity column. From here, simple LEAD to find the differences between consecutive rows and counting these.

The second part changed the calculation slightly to use groups of three rows. I copied my LEAD formula to include 3 rows instead of just 1 in each side of the calculation.

Day 2

We’re in a submarine, moving forward or up/down. The input was something I needed to evaluate in order again, so I repeated a similar load. Then I used SUBSTRING and CASE to decide what type of instruction was needed and sum the results.

Part 2 was tricky. I bailed initially, as I couldn’t quite get the math down in my head. I eventually set up a small test data set using the values on the site and then used that to calculate things. I had a series of CTEs that I used to extract the values, then get changes, then perform the math.

Letting part 2 sit for a day in my head helped me focus better.

Day 3

Day 3 was fairly easy binary counting. The test data doesn’t depend on order, so I just loaded it up. Then I need to extract the values into the bits, so SUBSTRING each of these out in a CTE. Not dynamic, but it was easy to extract all 12 bits, then count up the number of 1s and 0s, deciding which was more prevalent.

From there, a simple calc to assemble back the counts into a binary number and convert to decimal.

Part 2 is really about counting the 1s and 0s in each position, then creating a final binary number from this and converting back to decimal. I had to read carefully here, as you need to reduce your input set each time. I ended up looping here, as I couldn’t find an easy way to do this otherwise. I could have added some flag to ignore rows, but ended up with a temp table and deletes to get this done.

So far, easy, harder, then easier.

December 10, 2021
2020 Advent of Code Day 6
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 6. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

This is another weird string grouping issue. The data load is a mess, meaning that there are groups of data I need to consider, and the groups are separated by blank lines. However, each group has multiple lines.

Ugh.

Easier in Python, where I can load the data line by line and break things. I do that with this code:
```
for answers in open("2020\day6\day6_data.txt").read().split("\n\n"):
```
In SQL, it’s harder. I bulk load into a table, the cursor through the data.
```
DECLARE pcurs CURSOR FOR SELECT lineval FROM Day6 ORDER BY linekey;

DECLARE
     @val VARCHAR(1000) = ''
   , @groups VARCHAR(1000);

OPEN pcurs;

FETCH NEXT FROM pcurs

INTO @val;

SET @groups = '';

WHILE @@FETCH_STATUS = 0

BEGIN
     IF @val > ''
         SELECT @groups += ' ' + @val;
     ELSE
     BEGIN
         INSERT dbo.Day6_Groups (groupanswers) VALUES (@groups);
         SET @groups = '';
     END;
     FETCH NEXT FROM pcurs
     INTO @val;

END;
     INSERT dbo.Day6_Groups(groupanswers) VALUES (@groups);

DEALLOCATE pcurs;

GO
```
Once that is done, I again have to do things differently. Python is easy, where I count the values and add them up.
```
answers = set(answers.replace("\n",""))
part1 += len(answers)
```
In SQL, I need to distinctly find the values, for which I need a function of some sort.
```
UPDATE dbo.Day6_Groups
  SET deduppedanswers =  DBO.REMOVE_DUPLICATE_INSTR(1,groupanswers)
```
Once that’s done the answer is the sum of lengths.

Part 2

More complex here. Now I need to match up the common answers among the groups. In Python, this isn’t bad. I use the intersection method to find out what matches between the groups.
```
for answers in open("2020\day6\day6_data.txt").read().split("\n\n"):
    matches = set.intersection(*[set(answer) for answer in answers.split()])
    part2 += len(matches)
print("Part 2: ", part2)
```
Fairly simple here, with the grouping of the answers in a set.

SQL is hard.I reloaded the data, and separated each group by a comma. This gave me data I could split up, keeping each group in a group.

From here, I had a CTE for this, another to put these into groups of 2 strings by groupID. I then did a comparison for common characters across each group of 2 strings. This gave me partial matches, and I then compared all these in a group, taking the minimum number of matches. From here, I summed up the count of all the matches.
May 24, 2021
2020 Advent of Code – Day 5
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 5. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

This is an interesting problem, and one that’s simpler than it appeared at first. I started down the path of some hash bucket thing, moving to calculate rows before I got to the end and realized this is really a binary problem.

As a result, after I loaded the data, I started here:
```
SELECT 
   (SUBSTRING(d.SeatCode, 1, 1) * 64) +
   (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
   (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
   (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
   (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
   (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
   (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
   (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
   (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
   (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
  FROM dbo.Day5 AS d
```
Here you can see I broke this into two binary sections. The first 7 characters get you a row code from 0 to 127. The last 3 values get you a 0 to 7 value. I should have been clued in when I saw the 0s here. In any case, this gets me the two binary values.

The seat code is the row multiplied by 8 and then adding the seat. I took the above query, wrapped it with a CTE and then ordered by seat codes. This gave me the highest value, which solved the problem.
```
WITH cteAirplane( ROW, seat)

AS

(SELECT 
   (SUBSTRING(d.SeatCode, 1, 1) * 64) +
   (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
   (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
   (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
   (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
   (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
   (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
   (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
   (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
   (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
  FROM dbo.Day5 AS d
  --ORDER BY row desc
  )
  SELECT (row * 8)+seat AS seatID
  FROM cteAirplane
  ORDER BY seatID DESC
```
Part 2

The second part is a different problem. Now I need the seat codes, but I’m looking for a gap here. Meaning a missing seat code.

I decided to use LAG here. I altered my first CTE to calculate the seat code directly rather than returning the row and seat. Then I added this CTE:
```
cteValues (SeatID, diff)

AS

(

SELECT seatid, SeatID - LAG(SeatID,1) OVER (ORDER BY SeatID) AS diff

FROM cteAirplane

) 
```
This CTE found the difference between each subsequent Seat codes using the OVER() clause. My final query was looking for a diff > 1, which returned 1 row. That was the answer.
May 17, 2021
2020 Advent of Code–Day 4
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 4. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

We have another string parsing operation. We get a series of lines that represent a passport. Passports are separated by blank lines. Therefore, we can get 1-x number of lines representing a passport.

Ugh.

Python seems like a good place to start here. I loaded the file and then started to concatenate rows of data until I found a blank one.
```
file_handle = open('2020\day4\day4_data.txt', 'r')
passports = file_handle.readlines()
part1 = 0
currpassport = ""
for row in passports:
if row not in ['\n','\r\n']:
        currpassport += row.replace('\n',' ')
#print(currpassport.split(" "))else:
```
At this point, I have a passport I can look at, with all the various sections. I used another split, this time into a dictionary to get each item separate.
```
currdict = dict(x.split(":") for x in currpassport.split(" ") if x)
```
Now, I can count these. If there are 8, or if there are 7 and CID is one of them, I have a valid passport. Adding these up gets me the answer.

Part 2

This is very similar, but each part now needs validation. So, I take the same structure, but once I have passports, I assume they are valid and start to check each section. It’s really a series of IF statements for me.
```
            valid = 1
if ((int(currdict["iyr"]) < 2010) or (int(currdict["iyr"]) > 2020)):
                valid = 0
if int(currdict["byr"]) < 1920 or int(currdict["byr"]) > 2002 :
                valid = 0
if int(currdict["eyr"]) < 2020 or int(currdict["iyr"]) > 2030:
                valid = 0
```
These each could be functions, and I’d refactor that way, but I couldn’t come up with an easier way to do this. After checking if I have enough valid items, I tally another passport (or not).

Overall, this felt like busy work, not hard, but just a grind through each set of validation.
April 28, 2021