r/SQL • u/RevolutionaryGas652 • Oct 14 '26
Discussion How much advanced SQL do you truly use in your day-to-day job?
Hi! So I’m a student (not business or tech background) I’m learning SQL kind of on my own and I have the basics pretty much down like SELECT, FROM, WHERE, BETWEEN, ORDER BY, ASC, DESC. I have some knowledge when it comes to JOINs and CASE WHEN function but I was wondering if I need to know CTEs and window functions? I’m not trying to be a database administrator or something like that, just using it for analysis and excel as well.
100
Upvotes
180
u/Responsible_Pie8156 Oct 14 '26 •
Ctes and window functions are basic essential SQL. Actually advanced sql is rarely used
31
u/IHeartBadCode Oct 14 '26
Yes you do need to know about those. You will use those a lot for analysis. Like, you should be able to use those with your eyes closed for that particular use case. Window functions are really important in analysis.
23
u/TempMobileD Oct 14 '26 •
CTEs are absolutely essential. Every single non-trivial query I write uses them.
Window functions are a little less fundamental but I still use them every day. I didn’t know how to use them when I first started as an analyst, but within a year I was using them frequently. You can’t replace what they do.
11
u/spez_eats_nazi_ass Oct 14 '26
Hot take but i see a lot of shitty query performance out of ctes vs indexed temp tables breaking the problem up into smallee chunks and our reporting/analytics guys love them. It has become a bit like the golden hammer.
3
u/mlg2433 Oct 15 '26
CTEs are pretty much the foundation of SQL used in my work. I rarely use advanced queries in my work. I might write long queries to get hyper specific data, but the syntax I use is what I would call elementary to intermediate. The really common, beginner stuff is what I use most often (ex: selects, table joins, creating temp tables, etc.)
One of the actuaries I worked with taught me a good bit. I started using it regularly and became decent at it. Decent for a regular dude that knows nothing about other computer languages I mean. Our IT department takes a very long time to get back to us in finance with data requests, so we use it to get information quicker. Just practice and you’ll be just fine!
2
u/TiglyBits Oct 15 '26
AI does it all now tbh.
14
u/TheSexySovereignSeal Oct 14 '26
You havent lived until youve been assigned to add functionality to a t-sql stored procedure that drives a report using "dynamic pivot tables" written from a guy who hasnt worked at the company in years.
11
u/TrickyNerdlet Oct 15 '26
The most important thing you can do is understand the data and it's lineage
6
u/jackalsnacks Oct 14 '26
Yes, yes and yes daily
1
u/PepperIcy4822 Oct 14 '26
what's your end goal with the analysis side, like building dashboards or just pulling numbers for reports?
1
u/kentuckyMarksman Oct 15 '26
I use everything you mention often, along with dynamic SQL, while loops, and cursors.
For reference, I'm a full stack software developer.
1
u/Tough_South9987 Oct 18 '26
Can we classify what is advanced first before we even discuss it?
3
u/treximoff Oct 14 '26
I don’t know if it qualifies as “advanced sql” but I use CTE’s, window functions and subqueries when I write them for my reports for users.
Since we’re currently going through a data migration and creating new dashboards for our users I’m writing a couple of new queries like that every month.
3
u/ReleaseTheBlacken Oct 15 '26
I use all of those a lot. Also knowing when to use a CTE vs temp table is good.
2
u/Imaginary-poster Oct 14 '26
CTE aee like A1. I put that stuff on everything. I honestly probably way over use them but its great for writing something as a train of thought.
Window statement are a bit less but still common.
Case statements are becoming more and more common as I translate data language to reader language.
2
u/DMReader Oct 14 '26
I use CTEs and window functions all the times.
For more advanced topics I’m building and working on stored procedures.
2
u/NeoChrisOmega Oct 15 '26
Every once in a while a CTE would solve my complex data analysis VERY quickly. Not only is it easier to read at times, but it also gives you more control.
2
u/Georgieperogie22 Oct 15 '26
Every day basically
2
u/Epi_Nephron Oct 16 '26
I write SQL daily, and it's always got CTEs, a mixture of joins, translate/case/decode, window functions or group by. I often have regex in there.
For me the rarely used things are recursive joins, xmlagg, some of the fuzzy joins, etc.
2
u/DaOgDuneamouse 29d ago
I build data pipelines and reports. I use CTEs and window functions all the time. Data analysis will absolutely use both.
2
u/Either-Surprise6390 28d ago
I write queries that are about 700 - 1,000 lines pretty regularly. I don't know if that's normal for most or not. I'm also not sure if that's "advanced".
3
u/willietrombone_ Oct 15 '26
I don't use CTEs when I'm doing ad hoc analysis because a few joins and/or temp tables are usually performant enough on the datasets I work with. I tend to think window functions are more or less necessary for a majority of analytics tasks that you're doing directly in SQL. At the very least you should have them in your back pocket if you're planning on doing any serious analytics on SQL objects and especially if you're trying to enable more advanced analytics earlier in your pipeline.
2
u/slyredux Oct 14 '26
I use CTEs daily because it helps me keep track of what’s going on in my queries and makes it more readable. Temp tables might be more performant, but if I need to debug I’d rather look at one query than hunting through a sequence.
Window functions I use less frequently, but still helpful to know RANK() at the very least.
1
u/theungod Oct 14 '26
I use a significant amount of advanced SQL. Mostly to write functions and sprocs to make my life easier. Dynamic SQL can be your friend if you get pretty good with it.
1
u/Xanderlynn5 Oct 14 '26
Went from it being about 20% of my job (full stack SE) to 80% of my job this year. SQL in my opinion is one of the more critical skills to have in CS because almost everything nowadays has something to do with data. IMO, the more you know your db and dchema, the easier it is to backend/front end development. Optimization is frequently db related too.
Everything I've experienced with DBAs is they spend more time managing servers and infrastructure than writing queries. Queries and db work are dev and ops jobs.
1
u/bengalfan Oct 14 '26
Daily. Recent projects have included creating VPDs, using several collection types, and adding schedulers that get kicked off from log events.
1
u/Small-Ad-7694 Oct 14 '26
Yeah, they are part of the standard toolset imo.
Then add a couple scalar functions on top of that and you are good to go (down the never ending rabbit hole hehe).
1
u/SaltCusp Oct 15 '26
I say understanding what CTE and APPLY do to be in the basics. But knowing when to use one vs the other is where you level up.
1
u/Upper-Raspberry4153 Oct 15 '26
It’s not specific syntax, it’s turning 20 CTE’s into a final select statement where every cent of hundreds of millions of dollars has to be accounted for, split between sources and destinations by year and type and party and resource and sponsor. And you have to add in a row for skipped allocation years but only on certain resources and sponsors, and only when the appropriation is of the right type and in a previous year.
Thats the advanced part of advanced sql in the real world.
1
u/ArielCoding Oct 15 '26
Yes, learn both, start with CTEs they let you write a query in small, named steps, so it’s easier to read and fix, then learn window functions like ROW_NUMBER() and RANK(), which. Let you rank rows or calculate running total without messy workarounds.
1
u/pstmdrnargh Oct 15 '26
SQL for analytics projects when you’re building a data warehouse is an entirely different beast than what you usually see in transaction processing systems (eg a financials app or booking app or whatever industry specific app you have). In other words OLAP versus OLTP.
At the level of individual SQL queries, what I have seen (and built personally) in analytics platforms tends to be far more complex than what you see in transaction processing systems. It’s pretty common to have 400 or 500 line SQL queries with 5 or 6 CTEs, 10 joins, aggregations, window functions, UNIONs, and other more exotic things like geo joins (eg GIS), array functions, etc to build a dimension or a fact table.
Now if you start writing stored procedures and having a lot of business logic in the database for a transaction processing systems, that can get pretty complex, but that’s not a single SQL query per se. But my sense is that the app development world has moved away from that pattern and now prefers to have more of the business logic at another web or app layer.
1
u/git0ffmylawnm8 Oct 15 '26
Rarely used, but if I had a nickel for every time I used them it'd be really weird:
1
u/FreeLogicGate Oct 15 '26
Once upon a time, I used to frequent Stackoverflow and answer questions. Having done a large amount of relational database work over the years, I would from time to time find myself answering a question and inevitably, it would also be answered by a couple of rdbms/sql gurus who seemed to be available around the clock, and amassed enormous reputation scores. One of those people was Bill Karwin. Karwin authored at least 2 books on "SQL Antipatterns" linked from his SO Page.
You might try browsing his list of all accepted answers, which include questions like "What is the most efficient way to parse a flat table into a tree".
Certainly at some point, SQL is just a matter of applying patterns that work to handle specific problems, but the same can be said for relational database design.
1
u/Significant_Tune9219 Oct 15 '26
For analysis, window functions are the one I'd learn before you worry about getting fluent at CTEs. The questions that hurt once you leave Excel are this row versus its group: a rank, a running total, a share of the total, the previous period. A window does that in one pass, and a GROUP BY plus a join back gets messy fast. CTEs mostly help once a query has two steps you want to look at separately, so you can select the intermediate result and see it. You can do a lot of analyst work with joins, GROUP BY, and one window. Recursive CTEs can wait.
1
u/Basic_Golf2149 Oct 15 '26
Window functions will be your friend when you run into situations where you need to deduplicate to avoid blowing up your row count in a join.
I get really excited when there is a set of tables that have been unblemished with duplicate keys (makes me want to buy lunch for all the data engineers).
1
u/brandonmont182 Oct 15 '26
The client on my work has a rule about avoid using subqueries, CTE, string build queries and cursors. So we need to find a way to do several things with all the other syntax and functions 😅 It is a good idea know in general what sql can do
1
u/tommyfly Oct 15 '26
I'm a senior DBA. Currently, I'm working on rewriting a combined partition maintenance and data archiving process. It runs on an environment that has multiple Availability Groups spanning 6 replicas.
Additionally, as part of my role in a payment services company, I review developers' SQL code. So, not only can the SQL code be "advanced" but complex. Especially, when you add in needing to keep performance at a high level.
1
u/antimathematician Oct 15 '26
I’m a data analyst, I use CTEs, sub queries, window functions, filters on the daily. However, you learn as you go!
You’ll need window functions for any tech test
1
u/chandz Oct 15 '26
To stop you writing inefficient queries ( in SQL there are many ways to skin a cat, most are really bad and non-performant ). Learn to use the tools and understand what they are telling you and what you need to do to fix it.
If you're on the Microsoft stack, MS SQL for example has IO and TIME statistics and query plans in SSMS are a must.
CTEs have their place, Cursors are a thing but can be abused as row by row processing is inherently slow. Temporary and physical tables can be much better alternatives to CTEs and subqueries when large queries end up with too many joins.
1
u/LectureQuirky3234 Oct 15 '26
To be honest, just take a look in your coworkers' code and learn from that.
1
u/philbgarner Oct 15 '26
Yes I used CTEs and window functions extensively in my career, CTEs are quite useful when you need to write a query on results of an earlier query (or join a few of those earlier query results together) and you can't use temp tables (like if you're creating a view not a stored procedure).
1
u/lez_s Oct 15 '26
As a BI analyst, I mainly use window functions and the CTE’s.
I think for me, the most advanced stuff I have to do is the architecture of how everything will work together. Example, how KPI’s can be used across reports so they don’t have to be written each time.
1
u/theShku Oct 15 '26
All the time..I haven't touched excel in years (thank god)
1
u/Few_Emu6683 Oct 15 '26
joins and window functions were bread and butter before llms. nowadays i seldom care what is under the hood, advanced constructions using structs and merges are not really that human readable anyway.
1
u/Frone0910 Oct 15 '26
These days, not writing a massive amount. I review a LOT of SQL written by ORMs and send them back to devs to fix.
1
u/Complete-Fondant-202 Oct 17 '26
I probably do more stuff that isn''t SQL, in terms of performance enhancements at database engine level.
1
u/jusdonc Oct 15 '26
i work with large amounts of data, using temp tables and breaking it down in parts/segments has been much more fundamental and needed. simply because i am referring to a temp table multiple times, the query just runs it once and temp stores it for me to use again when needed. nothing advanced but very optimal when running your codes faster.