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

81–90 of 110 posts

Re: Advent of Code 2024 in pure SQL

#81

Post by wink »

dagss wrote: post #76

I have been writing a ton of SQL -- implementing a lot of the business logic of a (stream processing) application in it. I really really like it, especially that I bring the computation to the data instead of the data to the computation. I often talk to developers who hate that idea though. They want me to instead move all the data to the backend, for a massive IO hit, just so that the computations can be expressed i…

I can't put my finger on it but I think many people see this as two operating modes, for lack of a better term. The more monolithic and enterprisey your solution (and with bespoke DBMS), the more it leans towards anything more complex than an index and maybe a couple triggers - and the more microservices-y where every small service owns its own database (and only half of them are RDBMS) the less complex code is desired in the DB itself, because you're also migrating off of single instances/clusters a lot (just taking a relatively dumb data dump with you, or even just adding new replicas, ship-of-theseus-like).

Re: Advent of Code 2024 in pure SQL

#82

Post by youerbt »

qsort wrote: post #5
scop wrote: post #4

I reacted to this title the way I react a new menu item at Taco Bell: a strange mixture of desire, shame, and admiration for human ingenuity.

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.

I do AoC in SQL, I wish it was true. With Postgres, you have lots of regex/string manipulation functions that make it easy.

For me, the biggest problem was memory. Recursive CTEs are meant to generate tables, so if you are doing some maze traversal, you have to keep every step in memory, until you are done.

Re: Advent of Code 2024 in pure SQL

#83

Post by hu3 »

trjordan wrote: post #18

If you like this kind of degeneracy, I tried AoC in Google Sheets this year. I only made it to Day 6, and not even both stars every day. I'm pretty confident my Day 7 solution is correct, but I hit per-cell character limits on longer inputs. Enjoy :) oh but don't open it on mobile, some sheets crash the app https://docs.google.com/spreadsheets/d/10FY-89y19tnRM_EAAnCd...

I'm on my phone so I can't open it now.

Does it use Google App Script? Would be a way to gain extra power I think.

Re: Advent of Code 2024 in pure SQL

#84

Post by larodi »

bob1029 wrote: post #80
harrall wrote: post #70

Earlier quoted context omitted.

Haven’t written SQL in a while (and I used to write a lot) but I think SQL Server recursive CTEs are fairly unbounded so it’s just a Postgres limitation unfortunately. (I’m a fan of MS SQL but it’s Microsoft and also hard to financially justify for many companies. But if you ever get to use it, it is a very solid RDBMS, even if the rest of your stack is open source.)

The cost of MSSQL is largely controlled by how the system is designed and the complexity of the business. The model I am most familiar with is a 10-20 employee B2B SaaS startup running one "big" instance on a single vm in the cloud somewhere. If this is approximately all you require, then the cost should not be a dominating factor in your decision. I think "because Microsoft" is also really poor justification if we a…

MS SQL is one fascinating piece of software and much closer to commercial big bro offering such as Oracle and DB2, yet much more user friendly and convenient.

This sentiment against it really always comes only from people who have not used it or have never touched the enterprise version which is a very mature ecosystem with lotta features available for ages now.

Most of my career I’ve been dealing with DBs and MSSql is the easiest to admin perhaps being also tightly integrated with all scripting in the platform. It also runs Linux and is doing it better than the rest can say for running Windows.

Re: Advent of Code 2024 in pure SQL

#85

Post by larodi »

solumunus wrote: post #72
larodi wrote: post #42

Earlier quoted context omitted.

this is poor man's SQL to unwrap for business logic. SQL is such more more about everything else and so litte about the trouble with mapping business logic into storage.

Please elaborate.

CTEs can indeed align mismatched and denormalised storage to some E/R which follows some business logic. That much you can say. But CTEs allow for recursion and save time when you need complexity but you want to follow a precise step-by-step reasoning.

This dis not arise as a need to solve business mess, but as a need to skip DDL for views/mviews.

You can have arbitrary dimensions sliced in CTEs which does not immediately imply a goal of business schema alignment.

Besides the top SQL devs I’ve met don’t lose time to align schema at all, but write the DB table names and columns as they are, because well… because they were usually the people who created the mess this way.

Many reports such as month-to-month increase of sales or other statistical stuff is much more readable when implemented with CTes. Besides - older versions of DB software didn’t always have window functions or proper ranking, so problems such as top-n were not (and in some occasions still are not) trivial to write unless view/cte is used. We talking pages of single query here, not textbook examples.

So is really about dice and slice in a convenient way, but less about business schema or the ideal E/R

Re: Advent of Code 2024 in pure SQL

#86

Post by fifilura »

larodi wrote: post #85
solumunus wrote: post #72

Earlier quoted context omitted.

Please elaborate.

CTEs can indeed align mismatched and denormalised storage to some E/R which follows some business logic. That much you can say. But CTEs allow for recursion and save time when you need complexity but you want to follow a precise step-by-step reasoning. This dis not arise as a need to solve business mess, but as a need to skip DDL for views/mviews. You can have arbitrary dimensions sliced in CTEs which does not immedi…

Maybe I misunderstood, and if I did it makes for an interesting discussion, but your explanation is mainly about what CTEs can be used for not what they can not be used for.

In my experience, normalizing tables is mostly for simplifying inserts. When you write business logic it is rather the opposite, you want non-normalized data because this is where the data is all in one place. And this de-normalization is a great use of CTEs. As well as a general cleanup of the input data, which was my original point.

Re: Advent of Code 2024 in pure SQL

#87

Post by marxisttemp »

AdieuToLogic wrote: post #50
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.

> Most RDBMSs support recursive CTEs, it feels like writing Prolog with a slightly sadistic syntax. Which makes sense as both are declarative logic-based languages. IMHO, SQL and Prolog fundamentally have much in common.

I did a semester at the university of Edinburgh and took database systems and logic programming at the same time, and I definitely felt the synergy between them.

Re: Advent of Code 2024 in pure SQL

#88

Post by lelanthran »

knicholes wrote: post #46
larodi wrote: post #39

Earlier quoted context omitted.

but why? what would make you react at human ingenuity with shame and desire? is this something about you or something about them in particular? isnt the whole of HN about human ingenuity...? are we to feel Taco Bell menu about it all, what am I missing?

This is just a guess, but if the OP's reason is similar to mine, DBMSs should be reserved for managing databases and not implementing complex logic.

I do line of business apps right now.

It seems to me that we have the abstraction inverted when it comes to ORMs, which is why so many experienced devs dont like ORMs (but can't really articulate why).

Here's my take, in the context of business logic:

The schema represents the business rules. The ORM papers over the DB so that the code doesn't have to match the business rules. Then the dev implements those rules again in code, but poorly.

If you simply skip the programming language using something like Postgrest you end up with a declarative structure of the business rules.

The first problem is that most developers would rather hunt a bug or perform a modification on a 30k Java or C# or python or ruby program than in 5k of SQL.

The next problem is that tooling for SQL is crap. It's declarative so good luck stepping through the logic. The dialects are all different so editors aren't great at spotting errors.

The last major problem is that changing it is hard - you can't simply change the schema and redeploy because migrations have to be made on the existing data.

All of these are surmountable in some ways, but they certainly ain't easy.

Doing LoB apps have opened my eyes a lot: maybe 99% of logic is shorter when expressed in SQL. Of course, that means that the only use of an ORM in this case is connecting to the DB, sending the query, and sending back results.

Re: Advent of Code 2024 in pure SQL

#89

Post by upghost »

brettgriffin wrote: post #7
scop wrote: post #4

I reacted to this title the way I react a new menu item at Taco Bell: a strange mixture of desire, shame, and admiration for human ingenuity.

I'm as equally amazed by the solutions in this post's github repo as I am with Taco Bell's new chicken nuggets.

⊂ •͡˘∠•͡˘ ⊃

Suspicious. Need to investigate if taco bell has interesting ANSI SQL flavored chicken nuggets or I've been taken for a fool!

Re: Advent of Code 2024 in pure SQL

#90

Post by haolez »

phartenfeller wrote: post #75

Nicely done. I know this seems crazy at first but in my opinion big SQLs are one of the best ways to store complexity. The problem being complex is the issue. SQL is a standard, condensed, extremely performant, actually testable, and logical language. Sure, not anybody can instantly maintain it but that would be the same as if it was a lot of lines and functions in Java. The more lines the more risk for bugs. I also…

I get this feeling as well, but then again debugging large SQL queries can be very opaque. Things like pl/pgsql help, but then it starts becoming more and more like a normal programming language.
Post reply on HN