Voice of the DBA

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.