Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Advent of Code 2024 in pure SQL

databasearchitects.blogspot.com

Post reply on HN
Print view View original

21–30 of 110 posts

Re: Advent of Code 2024 in pure SQL

#21

Post by jamie_ca »

dunham wrote: post #17
qsort wrote: post #5

Earlier quoted context omitted.

I work a lot with databases and I've seen... stuff. It's not as bad as you might think if you know what you are doing. Most RDBMSs support recursive CTEs, it feels like writing Prolog with a slightly sadistic syntax. For something like AoC the most difficult part is probably parsing the input.

Speaking of parsing, back around y2k we were building an app that used XML everywhere, which was the style at the time, and our DBA wanted to write an xml parser in SQL (the api would involve sending XML to the database). That got vetoed. IMO, this kind of thing is what AoC is good for - you get to play with weird/obscure stuff without affecting your day job code.

I did something with JSON back before there was reasonable native support - it's certainly not robust, but it handled a few syntax variants for a use case where we had an extra attribute column that serialized JSON, and wanted to surface one of the fields as a proper column on the table.

https://blog.tracefunc.com/2011/11/19/parsing-json-in-sql-ht...

Re: Advent of Code 2024 in pure SQL

#22

Post by Rendello »

fifilura wrote: post #15
SoftTalker wrote: post #11

Over my career I've certainly written more SQL than any other type of code. Not so much in the last five years so I'm sure I've lost some of it, but I used to really enjoy it. Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

I second all of that! I wish more people would see the beauty. After a session of SQL, when I take a step back and think. "Hold on. What I have been doing lately is just pure logic. No library dependency resolution, no concurrency problems (even though massive concurrency is certainly under the hood). No mutability issues. Just logic." SQL obviously has its warts, some of them serious, like testability. But at the en…

> Maybe somewhere between SQL and Prolog is the future of programming.

Must be Datalog then ;)

Re: Advent of Code 2024 in pure SQL

#24

Post by bob1029 »

SoftTalker wrote: post #11

Over my career I've certainly written more SQL than any other type of code. Not so much in the last five years so I'm sure I've lost some of it, but I used to really enjoy it. Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

Over the years I have been pushing more and more responsibilities into the RDBMS. I now see things mostly in terms of ETL, SQL and schema. Virtually every conversation I've ever had about the application of technology to the business could be expressed in these terms. Business logic defined as SQL queries can be quite intuitive when the schema is structured well and aligned to the business stakeholders' perspectives.

Code, frameworks, ORMs, "best practices", patterns, et. al. are ultimately a distraction. There are a million ways to get the data in & out of the database. Moving the bits around is not valuable. There are many overblown software solutions out there that could have been a simple merge statement or CSV import job.

I think that a lot of the misconceptions and bad moods about SQL come out of being forced to work with nasty schemas. The language itself is really domain specific. Consider that one wouldn't complain as much about a super fucked up nested query (and resulting SQL syntax woes) if it wasn't necessary to write such a query in the first place. Aligning tuples and relations to the way the business typically talks about them means you will be less likely to be fighting these things over time. Often, it isn't possible to refactor the schema from zero, but you can put replicas/views around a "bad" schema and target it with your new development & refactors.

Re: Advent of Code 2024 in pure SQL

#25

Post by crazygringo »

SoftTalker wrote: post #11

Over my career I've certainly written more SQL than any other type of code. Not so much in the last five years so I'm sure I've lost some of it, but I used to really enjoy it. Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

> Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

I dunno... I've written a tremendous amount of SQL, and I still have to think imperatively (iteratively) in order to write queries that are actually performant, and to know which indexes need to exist.

It would be wonderful if I could just think in terms of set operations, but that tends to result in queries that take 5 minutes to execute rather than 5 milliseconds.

My entire thought process is basically -- what table do I start with, what rows in what order, joining to what, under what conditions, aggregating how, rinse and repeat... It's entirely a mental model of loops and aggregation, never of set operations.

Re: Advent of Code 2024 in pure SQL

#26

Post by fifilura »

bob1029 wrote: post #24
SoftTalker wrote: post #11

Over my career I've certainly written more SQL than any other type of code. Not so much in the last five years so I'm sure I've lost some of it, but I used to really enjoy it. Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

Over the years I have been pushing more and more responsibilities into the RDBMS. I now see things mostly in terms of ETL, SQL and schema. Virtually every conversation I've ever had about the application of technology to the business could be expressed in these terms. Business logic defined as SQL queries can be quite intuitive when the schema is structured well and aligned to the business stakeholders' perspectives.…

> Consider that one wouldn't complain as much about a super fucked up nested query (and resulting SQL syntax woes) if it wasn't necessary to write such a query in the first place.

And in "modern" SQL this is solved with CTEs. Use them to unwrap the schema the way you want it first, before implementing the business logic.

Re: Advent of Code 2024 in pure SQL

#28

Post by fifilura »

crazygringo wrote: post #25
SoftTalker wrote: post #11

Over my career I've certainly written more SQL than any other type of code. Not so much in the last five years so I'm sure I've lost some of it, but I used to really enjoy it. Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

> Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful. I dunno... I've written a tremendous amount of SQL, and I still have to think imperatively (iteratively) in order to write queries that are actually performant, and to know which indexes need to exist. It would be wonderful if I could just think in terms of set operations, but that tends to result in queri…

It may be true, until you do your ETL in an index-less database such as BigQuery or Trino. Postgres will always be faster for optimized, end user serving, queries.

But BigQuery allows you to scale it to 100s of CPUs without having to worry about indexes.

Re: Advent of Code 2024 in pure SQL

#29

Post by fifilura »

taberiand wrote: post #23
datadrivenangel wrote: post #12

The data parsing must have been painful. High wizardry.

I feel like it'd be entirely acceptable to load the inputs into tables first and still qualify as pure sql, because string parsing in sql is so blergh

Input in AoC is always well-formed. And you can always use a regexp? Seems like the smallest problem to me. As soon as you get past that recursive one row per line trick in the posted solutions

Re: Advent of Code 2024 in pure SQL

#30

Post by pearjuice »

Doing it in pure SQL is really impressive but I think the real tell-tale sign of peak "cracked engineer energy" is the maintained, decade-old blogspot site. Can't exactly put my finger on it, but really gives off "niche mastery". I don't even know the authors but I'm sure in the right circles a few dudes maintaining a blogspot site called "database architects" for a decade probably don't need an introduction.
Post reply on HN