2020 Advent of Code–Day 1

This series looks at the Advent of Code challenges.

I started the Advent of Code at the beginning of December 2020, but life quickly got in the way. Weekends especially, where I try to get away from the computer, so I fell behind. However, I did work through a few, and one of my goals in 2021 is to get through all of them.

I’m going to document my solutions on my blog.

Day 1

The first thing I did was set up a template for the solutions. This is clearly important, and I used some basic ASCII art.

2021-01-22 12_23_37-Day1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (53)) - Microsoft SQL Server Manag

From here, I tackled the challenge. This is one suited for databases, as there is the need to take a list of numbers and find two that add up to 2020. I created a simple table that contained a single column to store numbers.

CREATE TABLE Day1
(  datavalue INT)
GO

In here I inserted the test data from the challenge.

The easy way for me to tackle this quickly was cross join the numbers with a sum. I put this in a CTE, which gives me the sum of all individual numbers.

WITH cteCalc (a, b, sumab)
AS (   SELECT
                       a.datavalue, b.datavalue, a.datavalue + b.datavalue AS sumoftwo
        FROM
                       Day1 a
            CROSS JOIN day1 b)

Once I had this, in the outer query I added a WHERE that limited the results to the sum being equal to 2020, and for the column list, I produced the product.

WITH cteCalc (a, b, sumab)
AS (   SELECT
                       a.datavalue, b.datavalue, a.datavalue + b.datavalue AS sumoftwo
        FROM
                       Day1 a
            CROSS JOIN day1 b)
SELECT a, b, a * b AS solution
FROM cteCalc WHERE sumab = 2020;
go

This gave me the result.

As a hint, I used BULK INSERT to load the complete data from the test file into my table.

Part 2

Each challenge has two parts, with the same data. In this one, I had to find 3 entries that summed to 2020. I just added another cross join and this was solved.

WITH cteCalc (a, b, c, sumabc)
AS (   SELECT
                       a.datavalue, b.datavalue, c.datavalue,
                       a.datavalue + b.datavalue + c.datavalue AS sumoftthree
        FROM
                       Day1 a
            CROSS JOIN day1 b
            CROSS JOIN day1 c
    )
SELECT a, b, c, a * b * c AS solution
FROM cteCalc WHERE sumabc = 2020;
GO

All in all, an easy day. Now I need to solve this in Python.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

6 Responses to 2020 Advent of Code–Day 1

  1. Jeff Moden says:

    Interesting and just a thought… I realize the site only asks for correct answers and not performant answers but… putting on the ol’ Data Scientist hat for a minute (because performance always matters to me)…

    When you have a known sum of two non-negative numbers, it’s a hard fact that one of the numbers MUST be = the sum/2 and that, in a domain of non-negative numbers, none of the numbers can be > the sum.

    It’s also a known fact that a+b=sum but also that sum-a=b. What the latter does is it allows us to calculate what we need to look as the high number using only on the (low) numbers = sum/2 and <= sum.

    With that set of definitions in place, you come up with the following, which only produces 2 reads instead of the 788 that the CROSS JOIN produces…

    WITH cteLookFor AS (SELECT DataValue, LookFor = 2020 – DataValue FROM #Day1 WHERE DataValue = 2020/2 AND src.DataValue <= 2020
    ;

    Like

  2. Jeff Moden says:

    Lordy… this site absolutely slaughters any attempts at formatting code. Trying one more time.

    WITH cteLookFor AS (SELECT DataValue, LookFor = 2020 – DataValue FROM #Day1 WHERE DataValue = 2020/2 AND src.DataValue <= 2020
    ;

    Like

  3. Jeff Moden says:

    Ok… note to self… don’t post code on this site. 😉

    Like

  4. Jeff Moden says:

    What the heck… one final shot…

    WITH cteLookFor AS (SELECT DataValue, LookFor = 2020 – DataValue FROM #Day1 WHERE DataValue = 2020/2 AND src.DataValue <= 2020
    ;

    Like

  5. Jeff Moden says:

    It’s not even posting all of the code never mind in a readable format.

    Like

  6. way0utwest says:

    I’m guessing comments don’t escape anything, so likely something in code is breaking the display.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.