Advent of Code 2021 with PostgreSQL | Hacker News Reader

Advent of Code 2021 with PostgreSQL

github.com

210 pointscraigkerstiens29 comments

Open articleView on HN

Shameless plug, I've been doing this (without much success after day 8 or so) for a couple of years now. My rules are:

- 1 statement per part - No schema needed

https://gitlab.com/feike/adventofcode/-/tree/master/2021

Some others doing sql based solutions:

https://github.com/xocolatl/advent-of-code https://github.com/zr40/adventofcode

Your repo comes up a lot in the reddit threads. Day 4/1 was particularly great!

When AOC 2021 is done I hope I will find the time to compile the different approaches for each day and compare them.

My current list of repos is this:

- https://gitlab.com/autra/adventofcode

- https://gitlab.com/feike/adventofcode

- https://github.com/dflss/advent-of-code-2021

- https://github.com/mitchellh/advent-2021-sql

- https://github.com/qwesda/AOC2021-postgresql

- https://github.com/WilliamLP/AdventOfCode

- https://github.com/xocolatl/advent-of-code

- https://github.com/zr40/adventofcode

Should note this is from Mitchell Hashimoto, founder of Hashicorp

And the video walk-throughs for each one (on his Youtube channel) are a nice touch.

Baller move, Mitchell.

https://m.twitch.tv/anthonywritescode is also doing AoC in SQL if you want to watch someone doing it live (or in a VOD)

I find this painfully impressive. Relatedly, Mitchell seems like such a genuinely cool guy.

Anyone else think this year has been a bit tame so far? Last time i did this was back in 2019 and it was very challenging/fun.

It has been easier. Which means I get to bed at a sane time. But it's also letting some of my non-programmer (or novice programmer) friends keep up better and participate without as much frustration (as 2019, half those problems were barred to them because they couldn't figure out the IntCode stuff).

My view is whether it's tame or not often depends on the tool you pick for the job.

Picking something way out of your comfort zone will force you to learn a ton as you go along.

Agreed - I decided to try learning Emacs Lisp to solve this years problems and I have been struggling on problems I know I could have solved in minutes with a more familiar language - but it makes finally succeeding that much sweeter

Probably. Last year I picked Zig and this year I picked Crystal and Crystal certainly is a lot easier to use for AoC for someone who is totally new to the language.

Ha! I’m doing it in kdb+/q this year. I can feel that statement in my bones.

I suppose maybe I should switch tools then. I started with using my favorite language on it this year because I struggled in the past (IntCode). This year everything seems easy though. Days 6 and 7 were the first where the answer wasn't immediately obvious but even then the solution is just "use this very basic data structure" or "use this formula that everyone knows."

It has at least been nice checking out the Reddit every night and seeing everyone else's code and micro optimizations I missed or different paradigms.

This is the first year I'm not being burnt out by AoC. Everyone I know doing the puzzles has so far either been a 10 % rockstar developer or had enough free time due to unemployment, illness or for some other reason. This year they seem to cater to us mortals, too...

The phrase "10% rockstar developer" might be accidental but I'm stealing it anyway.

I recently went back and completed 2016 (from scratch), 2017 (was half-done) and 2018 (couple of missing problems). I think it’s largely on par with those so far - the first 10 are always quite straight-forward. It’s around day 15 that you start encountering the sort of problems that require a bit more thought.

You should try 2019!

To be clear that was just filling a couple of gaps :) Ihave also done 2019 and 2020. 2019 has a couple of tough ones but only starting in the teens iirc. I understand some people found IntCode to be tough and there were a couple of IntCode ones early-ish, but I didn't find them that bad really.

I much prefer it this way. I want to solve a small puzzle before work, not grind goblin combat details (2018-15 ugh) for the whole evening. No shortage of tougher puzzles out there for people who want flex their CS PhD

Wow, this is so cool! My goal of this year is much more simpler. Hopefully this year, I will solve all of them using a Functional programming Language named Elixir [0]. So far, I'm loving it! The source code is available on GitHub [1]

The repository also contains couple of mix tasks to generate boilerplate codes for each solve. And update the readme automatically on each commit to list the problems that I've solved so far.

[0] Elixir: https://elixir-lang.org/

[1] Github: https://github.com/wasi0013/advent_of_code

Oh! I almost forgot it is worth mentioning the creator of Elixir Language "José Valim" also solves these problems and streams them on twitch [0].

Those are helpful to learn different approaches to solving these problems.

[0] Github: https://github.com/josevalim/aoc

[1] Twitch: https://www.twitch.tv/collections/k_DLnk2tvBa-fQ

I think its awesome that many people find ways to solve these with different rules making the challenge harder. As a person that is a couple of years into programming, I am also going in with som personal rules. I try to solve this years AoC with as few if statements as possible using only Pythons standard library. I have completed day 1 to day 4 with 0 if statements. https://github.com/emilbratt/adventofcode/tree/main/2021 Having said that, I don`t agree on changing the input files as I see many people do. I don`t know if it is feasible to solve in SQL without doing so, so take it with a grain of salt. For instance, take day 4s input. I`ve seen many split the random numbers into one file and the bingo board numbers into another. I`ve also seen people using their IDE to remove all leading white space for the single digits for the bingo board numbers to make it easier to parse programmatically. In my opinion, this defeats a major purpose of the challenge. It is in fact a programming challenge.

I tried to complete all of them in Spreadsheet, so far only Day 5 is quite tough and Google Sheet could not handle it.. https://twitter.com/chenyuio/status/1467750483494273025 (Day6- in separate sheet to avoid spoilers)

You can use regexes to help you split the file into pieces (this is in PostgreSQL), I expect other dbms's to have similar functions available.

    SELECT
        lineno::int              AS line,
        ((lineno-3)/6)::smallint AS card,
        ((lineno-3)%6)::smallint AS y,
        (col - 1)::smallint      AS x,
        value::smallint          AS value
    FROM
        regexp_split_to_table($1, '\n') WITH ORDINALITY AS sub(line, lineno)
    CROSS JOIN
        regexp_split_to_table(ltrim(line, ' '), '(\s+|,)') WITH ORDINALITY AS sub2(value, col)
    WHERE
        line != ''
        AND value != ''

I did not know about regexp_split_to_table() or regex functions in general in dbms, but that is very neat.

Felipe Hoffa did a lot of last year’s Advent Of Code in Snowflake SQL. It’s worth a look - https://towardsdatascience.com/sql-and-adventofcode-2021-on-...

Where's the answer for day 1, part 2? Here's day 1 - https://github.com/mitchellh/advent-2021-sql/blob/main/day01...

I can't see where it is creating a window of three results to compare against. I guess, you might just be able to change the offset to 3, as the offsets of 1 and 2 will be included in your current window so don't need to be compared.

Heh I wondered the same thing, but the README says he may not complete every challenge. I actually did the first problem in SQL, and learned a bit about window functions and bounding the range it covers:

https://github.com/jarshwah/advent-of-code/blob/main/sql/202...

Though now I know you can ignore the window and just compare against the 3rd offset, the first solution just needs a `LEAD(depth, 3)` to complete the second question.

Still - I learned about `RANGE BETWEEN` so I'm calling it a win.

I'd love to know why the author (I've seen that elsewhere as well) would aim at not writing some plpgsql? Is it for the "tour-de-force" bragging rights? I've found that a plgpsql routine can be so much more efficient and faster to devise; especially with good use of triggers. Executing schema-related logic in "ring-0" is way faster and the transaction guarantees integrity? Isnt guaranteed integrity with some logic "vagrant-ish"? :-)

BTW awesome products Hashicorp, way to go!

Reply on news.ycombinator.com