I feel like the word “partitions” used in this article should be “window functions”
Why most developers stop learning SQL at subqueries - a 5-minute guide for PARTITION BY and CTEs
Submitted 9 months ago by testeronious@lemmy.world to programming@programming.dev
https://zaidesanton.substack.com/p/the-most-underrated-skill-sql-for
Comments
mvirts@lemmy.world 9 months ago
GBU_28@lemm.ee 9 months ago
Good point. Window functions are mandatory knowledge for intermediate users
AngryishHumanoid@reddthat.com 9 months ago
I imagine it depends on use case. Anything more advanced that subqueries/CTEs is largely the domain of ETL developers (not exclusively, obviously) but most others using SQL don’t need anything that advanced.
GBU_28@lemm.ee 9 months ago
CTEs enhance readability, documentation, and testing imo. I’m very pro CTE whenever a subquery isn’t the obvious choice.
howrar@lemmy.ca 9 months ago
It’s always bothered me when I write subqueries that have to be run on each row. Good to know that there’s a better way.
PlutoniumAcid@lemmy.world 9 months ago
I have to create a fucking account just to fucking read your article?! Kthxbye!
mac@programming.dev 9 months ago
Lmaydev@programming.dev 9 months ago
Lol
But just that popup existing is irritating.
schema@lemmy.world 9 months ago
Very useful. I’ll definitely going to use the ranking stuff.
Lmaydev@programming.dev 9 months ago
CTEs are amazing. I discovered them semi recently and it’s such a great tool to have in your belt.
Partitioning is definitely something I need to work on.
abhibeckert@lemmy.world 9 months ago
You say that like it’s a good thing. I like my queries simple. Also - the stuff you have under “stage 6” should be “stage 2” in my opinion.
fmstrat@lemmy.nowsci.com 9 months ago
Huh? How do you write an efficient query and not take into account joins? A single table query? Are you a Prisma fan?
FooBarrington@lemmy.world 9 months ago
Ooooh, that’s a good burn
Lmaydev@programming.dev 9 months ago
Joining is one of the simplest and most useful things in SQL.
How can you possibly query without it?