I’ve always felt that SQL is somewhat easy to grasp for basic queries, but gets complex and difficult for even moderate to higher complexity use cases. My eyes glaze over when I read long stored procedures that someone else has written. Any recommended resources to go from beginner/beginner-intermediate to advanced?

The way to learn advanced SQL is to challenge yourself to find a set oriented solution and avoid procedural code. The more unreasonable it feels, the more you learn.

If the solution you find is longer and not much faster than the procedural alternative, you throw it away and fall back on procedural code.

Stored procedures are not advanced SQL. Most of them are not SQL at all. There are a few legitimate reasons for using SPs such as reducing roundtrips to the database and writing little pure functions for use in SQL statements.

But many uses of SPs are just laziness or a symptom of organisational dysfunction.

My advice is: don't write complicated SQL.

The best thing I learned about SQL is that it can do an awful lot of clever stuff but that the vast majority of the time you really don't need it. Learn the basics. Shrug the rest off.

I feel like stored procedures and co crosses over into the realm of application programming, and while I can't speak from experience (so take this with a huuuuge grain of salt), this is where things break down. It feels like adding logic / basic programming to JSON/YAML, which are data/config languages primarily.

I think stored procedures - or anything that goes beyond storing / looking up data - had a place when a database had multiple different clients, but with modern day systems that's less likely to be an issue.

PRQL, or https://www.malloydata.dev/