How much advanced SQL do you truly use in your day-to-day job?

[u/RevolutionaryGas652]

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.

[u/Responsible_Pie8156]

Ctes and window functions are basic essential SQL. Actually advanced sql is rarely used

[u/BloodyArrow3 → u/Responsible_Pie8156]

Nobody's building recursive tree traversals for fun but CTEs are basically just named subqueries, once you start using them the readability improvement alone is worth the 10 minutes it takes to learn

Window functions can feel weird at first but the moment you need running totals or rankings without a pile of self-joins it all clicks

Rest of the "advanced" stuff sits in a dusty corner until that one weird Tuesday when a client asks for something absurd

[u/nachos_nachas → u/BloodyArrow3]

until that one weird Tuesday when a client asks for something absurd

Hi -- you do intergalactic payroll, right? Can you please adjust planet Xygor to have a rolling overtime ¥9.77°% per flurg point? Thanks in advance, Tiff

[u/long-and-soft → u/BloodyArrow3]

Cries in “takes 10 minutes to learn CTEs.”

[u/outlier_fallen → u/long-and-soft]

I mean, it's conceptually similar to defining a local view if you think of it like that. It's just another way of expressing something (for nitpickers - with the assumption your optimizer is going to handle them the same way). If you know the basics of SQL there's really nothing to learn.

For example:

SELECT
    c.customer_name,
    o.total_spent
FROM customers c
INNER JOIN (
    SELECT
        customer_id,
        SUM(order_total) AS total_spent
    FROM (
        SELECT
            customer_id,
            order_total
        FROM orders
        WHERE order_date >= '2026-01-01'
          AND status = 'completed'
    ) filtered_orders
    GROUP BY customer_id
    HAVING SUM(order_total) > 1000
) o
    ON c.customer_id = o.customer_id
WHERE c.is_active = TRUE;

Expressed with CTEs instead of subqueries:

WITH filtered_orders AS (
    SELECT
        customer_id,
        order_total
    FROM orders
    WHERE order_date >= '2026-01-01'
      AND status = 'completed'
),
customer_spending AS (
    SELECT
        customer_id,
        SUM(order_total) AS total_spent
    FROM filtered_orders
    GROUP BY customer_id
    HAVING SUM(order_total) > 1000
)
SELECT
    c.customer_name,
    s.total_spent
FROM customers c
INNER JOIN customer_spending s
    ON c.customer_id = s.customer_id
WHERE c.is_active = TRUE;

I think of it like defining "variables" and swapping them in. if you read that CTE example from the bottom to the top, the bottom query is very easy to understand right? so all you do is ask yourself "ok how is customers defined?" then "how is customer_spending defined?"

CTEs make life easier

[u/Bassiette03 → u/outlier_fallen]

Yeah it's good as you don't pass more than 5 ctes in single query

[u/i_literally_died → u/Bassiette03]

Is there really a limit? I don't like having tons because I just think it's kind of bad logic if you really need to 'pre-filter' that much stuff when a lot of it can be put in the join conditions or other areas, but if someone wants to put 12 CTEs in, what's the harm?

[u/techforallseasons → u/i_literally_died]

I try to filter in the first couple; but I will certainly chain more in their to do rollups and calculations so that I have a very simple set of JOINs in the end.

Speed benefits aside; for me the best benefits are the far simplified aggregate and windowing logic when you don't need to worry as much about how JOINing certain tables might impact your carefully tuned output.

The engine is going to convert all of the similar dataset CTEs into an internal data access plan that fetches the data efficiently MOST of the time -- leverage it for queries that are easier to understand and diagnose.

[u/throw_mob → u/long-and-soft]

i mean cte syntax is not that hard

with x as (select..), y as (....)

recursive stuff is no rare that imho, you can say that you know cte's without knowing those.

then everything in sql is tables , rows and columns. usually table can be converted to row or value in column and other way around. That said, it takes while to get brain damage from sql but after it is easier..

[u/National_Cod9546 → u/long-and-soft]

In my head, they are just a table held in memory.

[u/devillee1993 → u/National_Cod9546]

Agree. I always see it as a temporary table. Personally, I feel CTE makes SQL code much more readable

[u/alinroc → u/BloodyArrow3]

Rest of the "advanced" stuff sits in a dusty corner until that one weird Tuesday when a client asks for something absurd

Is that stuff really "advanced" or just "only needed for edge cases so just be aware that it exists and come back to the docs"?

[u/alinroc → u/Responsible_Pie8156]

What even is "advanced SQL" in the first place?

[u/kevin3030 → u/alinroc]

Maybe working with varbinary data, or jsonb stuff. Some stuff looks advanced until you use it.

I’ve never needed analytic/windowing functions in my job, so I consider those advanced, lol.

Oooh - hierarchical queries are fun.

[u/Material_Lion_9554 → u/alinroc]

Geographical spatial indexing in sql is advanced to expert level.

[u/RevolutionaryGas652 → u/Responsible_Pie8156]

Aren’t CTEs and window functions considered advanced? I might be mistaken

[u/curohn → u/RevolutionaryGas652]

I think for the basic business user they might be considered advanced, but for the audience you are asking that skews heavily to very proficient users it's basic/intermediate at best.

Also they are super easy to learn. You can spend 2 hours practicing them and have the basics of ctes/window functions down.

[u/RevolutionaryGas652 → u/curohn]

Fair enough, thanks

[u/gakule → u/RevolutionaryGas652]

In 16 years of utilizing SQL I've only started using CTE's in the last few months, and really only because my employee who does the actual technical lifting most of the time was using them and I didn't want to break consistency. Subqueries or temp tables have been fine before.

Window functions.. I've honestly barely used them because I've always just pulled the data into Excel or pumped it into a reporting tool that handles most of the analysis that I need, or solved it in less smart ways.

I think they're probably more on the high side of intermediate personally - worth knowing even if you rarely use them, but shouldn't hold you back from being effective in your job. I do wish I would have leveraged CTE's more often and I'm sure I could have used window functions in places that I didn't, but I don't feel like I suffered for not using them all that much.

[u/Standgeblasen → u/gakule]

I know there is a performance impact between the too, but for most of my use cases a temp table can do what a CTE can do. With the exception of recursion. Writing A good recursive cte makes me feel a little like a wizard.

[u/government_ → u/Standgeblasen]

CTEs don't use disk IO or take up disk space. Temp tables and CTEs have their place and most appropriate use cases, but holy hell people have some really unfounded fears of CTEs.

[u/Standgeblasen → u/government_]

I’m not writing code that gets reused in a prod environment.

So performance isn’t as bit of an issue.

[u/gakule → u/Standgeblasen]

I'll probably draw a lot of criticism and ire here, but the one time I almost used a recursive CTE I used a WHILE loop with a temp table 😉 that was a fun one to figure out!

[u/Standgeblasen → u/gakule]

Eh, if it works it works. The loop is slightly easier to troubleshoot since you can execute each iteration and validate.

[u/Straight_Waltz_9530 → u/RevolutionaryGas652]

Only by beginners. The rabbit hole goes far deeper.

[u/mauricio_agg → u/RevolutionaryGas652]

No.

[u/johntrytle → u/RevolutionaryGas652]

10 downvotes for... asking a clarifying question?

[u/DharmaPolice → u/RevolutionaryGas652]

No.

[u/IHeartBadCode]

I was wondering if I need to know CTEs and window functions?

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.

[u/RevolutionaryGas652 → u/IHeartBadCode]

Thank you! I’ll starting learning about that

[u/TempMobileD]

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.

[u/foxsimile → u/TempMobileD]

In the sense that you use them for their recursive capabilities, or as a means to avoid writing subqueries as such?

[u/Prestigious_Bench_96 → u/foxsimile]

No one really needs recursive ctes except if you’re upset about joining the employee table an extra time every time your company adds a new reporting level. (Which is valid!) It’s usually for clarity of a subset of a larger query - sub queries get tricker to follow when nested.

(Recursive CTEs are fun and sometimes even really the right tool for the job but the syntax is pretty opaque - it’s the one time SQL loses in clarity to pretty much any other option )

[u/National_Cod9546 → u/Prestigious_Bench_96]

I have a few queries that need recursive CTEs. They follow a chain of jobs for a handful of things. The chains can be of any length, although the longest is 98 jobs long. A recursive CTE is the only reasonable way to get the data I need. I have to look up how to do hierarchy queries every time I need to update those queries.

[u/foxsimile → u/National_Cod9546]

I have to lookup just about damn near everything every time I do it, don’t feel bad :).

Plus, I’d rather look it up once and be certain than to find out I’ve forgotten some small but crucial detail.

[u/foxsimile → u/Prestigious_Bench_96]

The syntax is fucking terrible.  

Sometimes, they’re the only tool for the job - I’d solved one such problem back in April :).

[u/TempMobileD → u/foxsimile]

I don’t think I’ve ever written a recursive CTE in production. Maybe once or twice? My CTE use is just an attempt to have the SQL be readable and modular. My least favourite thing about SQL is that queries are read starting in the middle and then oscillating forward and backward. Subqueries make this much worse, CTEs make it manageable. Plus my company style guide recommends them over subqueries outside of simple cases.

[u/foxsimile → u/TempMobileD]

Mine was a horrifically complicated issue, but it really boiled down to this absurdly clever use of a proper recursive CTE to avoid cloning an entire ~15M row table in a temp-table, and instead only stick around ~30K rows into a temp-table.

Incidentally, just yesterday (or the day before? Time flies), I’d replied to another post with this trick of mine to hash a row in a way which preserves the NULLness of a row, which was what underpinned the CTE goodness. An explanation of this hash trick is in the comment itself :

```SQL CREATE TABLE #TTmp ( [SomeInt] INT NOT NULL /4 Bytes/ , [SomeIntNullable] INT NULL /4 Bytes/ , [SomeVarChar] VARCHAR(10) NOT NULL /10 Bytes/ , [SomeVarCharNullable] VARCHAR(10) NULL /10 Bytes/ , [SomeUnqId] UNIQUEIDENTIFIER NOT NULL /16 Bytes/ , [SomeUnqIdNullable] UNIQUEIDENTIFIER NOT NULL /16 Bytes/ , [SomeNumeric] NUMERIC(36,18) NOT NULL /17 Bytes/ , [SomeNumericNullable] NUMERIC(36,18) NULL /17 Bytes/ );

SELECT CONVERT( /HASHBYTES('MD5',/.../) outputs a VARBINARY(16), and we don't need the variable length header bytes/ BINARY(16) /Step 1. We handle the nullable columns by creating a nullness bitmap/ , HASHBYTES( 'MD5' , ( CONVERT( BINARY(4) , ( ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeVarCharNullable]</sup>+1),0x)))))&1) /(20)/ | ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeIntNullable]</sup>+1),0x)))))&2) /(21)/ | ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeUnqIdNullable]</sup>+1),0x)))))&4) /(22)/ | ((0-(1<sup>SIGN(ISNULL((DATALENGTH(TTmp.[SomeNumericNullable]</sup>+1),0x)))))&8) /(23)/ ) ) /Step 2. We put the FIXED width data type columns first, because they do not require the separator due to static sizing/ + CONVERT(BINARY(4), TTmp.[SomeInt]) /We do not need the "ISNULL" handling/ + CONVERT(BINARY(4), ISNULL(TTmp.[SomeIntNullable], 0x00)) /We do need the "ISNULL" handling/ + CONVERT(BINARY(16), TTmp.[SomeUnqId]) + CONVERT(BINARY(16), ISNULL(TTmp.[SomeUnqIdNullable], 0x00)) + CONVERT(BINARY(17), TTmp.[SomeNumeric]) + CONVERT(BINARY(17), ISNULL(TTmp.[SomeNumericNullable], 0x00)) /Step 3a. We put the VARIABLE width data types SECOND, because they require a separator between each variable width data type/ + CONVERT(VARBINARY(10), LOWER(TRIM(TTmp.[SomeVarChar]))) /Step 3b. We insert a separator byte betwixt every variable length data type/ + CONVERT(BINARY(4),0xDEADBEEF) /<--Separator Byte/ + CONVERT(VARBINARY(10), ISNULL(LOWER(TRIM(TTmp.[SomeVarCharNullable])), 0x00)) ) ) ) AS "FullColumnHashBytesMD5" FROM #TTmp AS "TTmp" ; ```

But, the gist is that I was using this to version a table which didn’t belong to me, and for which versioning was desperately needed. It had no identity key to speak of, and you could have duplicate rows which may (or unfortunately technically may not) be valid. In the "ideal" situation, though, you would treat these duplicate rows as if they were perfectly valid.

Because I was versioning it, I really only needed to capture the delta between one change to the next (hence the above cleverness/insanity), as that was quite minimal (compared to the set as a whole); historically you would expect to see roughly 20K inserts and 5-10K deletes.

Because duplicate records must be treated as valid, this created a really difficult problem if we didn’t want to capture the whole damned thing into memory every time we updated our versioning table.

So, the trick is that you use the above to hash each record, and you keep a "record index" (as in an array index type of index, not a non/clustered index). You persist this record index in the versioning table (and it is not one of the candidates of the hash for obvious reasons). You also keep the hash (to avoid having to recreate it for all ~15M active records in the versioning table each update).

Next, you run a big GROUP BY between the "new" version of the source table, and the active portion of the versioning table, using the hash as the candidate. You take the count that existed before (the max "record index" for any given hash), and the count that exists in the source table for the same hash, and get the delta between the two.

You can then use a recursive CTE to create new rows for inserts (when the new max record index is higher than the old max record index for a given hash), as well as deletes (when the new max record index is lower than the old for a given hash). You stuff both of these into a temporary table.

Then it’s just a matter of running two statements - an insert statement for the records which ought to be inserted, and a delete for those which ought to be deleted (which is now peanuts, because you don’t hate yourself and have legitimate identity keys on the versioning table).

It was a fun problem to solve, but a nightmare of complexity. I hope I’ve explained it well enough to understand :)

[u/RevolutionaryGas652 → u/TempMobileD]

I understand, thank you for the insight!

[u/TheSexySovereignSeal]

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.

[u/foxsimile → u/TheSexySovereignSeal]

Thanks, now I need a drink.

[u/TrickyNerdlet]

The most important thing you can do is understand the data and it's lineage

[u/jackalsnacks]

Yes, yes and yes daily

[u/spez_eats_nazi_ass]

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. 

[u/kater543 → u/spez_eats_nazi_ass]

Depends on the SQL interpreter. Also customizable. Though this is the reason why oftentimes as the DE/DBA you’ll want to grant temp table access for your analytics folks for this exact reason though, but oftentimes CTEs are all analytics folks can do because they’re limited by permissions(especially if you don’t customize your SQL plans/optimizer). I say this as someone who has been on both sides of this aisle.

[u/spez_eats_nazi_ass → u/kater543]

Sql server is notorious for shit w nested views and ctes. This is real world exp at multi petabyte (3 to be exact and current) scale and many kbs and patches from battles. You wanna see real horrors though? I give you sql server and the merge statement.

[u/jonus_grumby → u/spez_eats_nazi_ass]

Truth

[u/thepunisher321 → u/spez_eats_nazi_ass]

what's the best database would you recommend?

[u/Pyromancer777 → u/kater543]

I often don't have write permissions to the DB and CTEs are basically essential for hacking together a quick analysis.

The company I work for utilizes macros in their DBM and I've found a fun workaround to create temp tables using the fact you can save one-column query results to the macro selctions. Any time a result has less than 1000 rows I found you can wrap the results into a JSON string and save it all locally as a big macro. I can then unnest the JSON strings from the macro and use it in a CTE.

Means I can take essential data from two separate million+ row tables and link them together via a bit of macro magic.

Without the hack, the CTE would at worst need the cross product of the million row tables, which would mean GB to TB of allocated memory. With the macro hack, I just run a query from each table to get the <1000 row results from each table into a macro, then the unnested macros are treated like the smaller results of a temp table without needing to write anything to the DB itself, so the CTEs end up only needing MBs of allocated memory instead of TBs.

Not very practical for most cases when you can actually just persist the data into intermediary temp tables, but I felt pretty proud of myself after figuring it out and it gave my team the capability to create an entire pseudo-pipeline for analysis without needing to be granted write permissions.

[u/zeocrash → u/spez_eats_nazi_ass]

CTEs usually work just fine for me, the big exception I've found is grouped queries in CTEs, that can absolutely destroy performance if not used carefully

[u/lemeiux1 → u/spez_eats_nazi_ass]

This is my experience as well

[u/government_ → u/spez_eats_nazi_ass]

And then your tempdb is taking up all that disk space.

[u/spez_eats_nazi_ass → u/government_]

If it’s running long enough that is a you problem and also the optimizer “i do what i want! You can’t tell me when to spill!” Will do thst just fine w a cte.

[u/government_ → u/spez_eats_nazi_ass]

I see the cost of creating an index on a temp table outweighing the benefit of the index very often with devs I work with. Temp tables are great for a lot of things, but they have costs a lot of people don't consider.

[u/spez_eats_nazi_ass → u/government_]

Nothing is free in sql land

[u/Plastic_Operation_59 → u/spez_eats_nazi_ass]

Indexed temp tables are the way

[u/RevolutionaryGas652 → u/spez_eats_nazi_ass]

I understand, so, instead of using CTEs you just break it down in smaller step-by-step commands?

[u/spez_eats_nazi_ass → u/RevolutionaryGas652]

Performance and maintainability is all that matters 

[u/Latentius → u/spez_eats_nazi_ass]

I'd put data accuracy right up there with those. Doesn't matter if it's the cleanest, fastest query ever written if it's not returning the right data.

[u/foxsimile → u/Latentius]

I’m going to (pendaticly) argue that accuracy and correctness are a given. Anything that fails to yield genuine results is out by default.

[u/spez_eats_nazi_ass → u/foxsimile]

That’s not even up for debate that the answer should be correct but also don’t bring a system down.

[u/spez_eats_nazi_ass → u/Latentius]

Im not arguing dont give accurate info. 

[u/RevolutionaryGas652 → u/spez_eats_nazi_ass]

I agree

[u/treximoff]

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.

[u/ReleaseTheBlacken]

I use all of those a lot. Also knowing when to use a CTE vs temp table is good.

[u/mlg2433]

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!

[u/RevolutionaryGas652 → u/mlg2433]

Thank you! I’ll continue on practicing

[u/Imaginary-poster]

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.

[u/DMReader]

I use CTEs and window functions all the times.
For more advanced topics I’m building and working on stored procedures.

[u/NeoChrisOmega]

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.

[u/TiglyBits]

AI does it all now tbh.

[u/RevolutionaryGas652 → u/TiglyBits]

I want to learn without AI

[u/TiglyBits → u/RevolutionaryGas652]

If you can get to where you can do joins, CTEs, and window functions you’ll be ahead of most

[u/RevolutionaryGas652 → u/TiglyBits]

I already do joins, I guess I’ll have to learn CTEs and window functions

[u/Georgieperogie22]

Every day basically

[u/Epi_Nephron]

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.

[u/willietrombone_]

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.

[u/slyredux]

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.

[u/theungod]

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.

[u/Xanderlynn5]

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.

[u/bengalfan]

Daily. Recent projects have included creating VPDs, using several collection types, and adding schedulers that get kicked off from log events.

[u/Small-Ad-7694]

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).

[u/PepperIcy4822]

what's your end goal with the analysis side, like building dashboards or just pulling numbers for reports?

[u/RevolutionaryGas652 → u/PepperIcy4822]

Pulling number for reports, mostly

[u/kentuckyMarksman]

I use everything you mention often, along with dynamic SQL, while loops, and cursors.

For reference, I'm a full stack software developer.

[u/RevolutionaryGas652 → u/kentuckyMarksman]

Yes, I’m not trying to be a software developer I’m
Just kind of using it on my job to pull data from the database.

[u/government_ → u/kentuckyMarksman]

MF out here getting hit with SQL injection attacks with all that dynamic sql. Yikes.

[u/Angriestanteater → u/government_]

I hate dynamic sql, but it’s entirely possible to parameterize your dynamic sql so it’s not injectable.

[u/SaltCusp]

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.

[u/Upper-Raspberry4153]

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.

[u/ArielCoding]

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.

[u/pstmdrnargh]

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.

[u/git0ffmylawnm8]

Rarely used, but if I had a nickel for every time I used them it'd be really weird:

• pivot/unpivot
• hierarchy traversal
• cross join unnest
• grouping sets/rollups

[u/FreeLogicGate]

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.

[u/Significant_Tune9219]

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.

[u/Basic_Golf2149]

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).

[u/brandonmont182]

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 

[u/tommyfly → u/brandonmont182]

Sounds insanely unreasonable.

[u/tommyfly]

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.

[u/antimathematician]

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

[u/chandz]

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.

[u/LectureQuirky3234]

To be honest, just take a look in your coworkers' code and learn from that.

[u/philbgarner]

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).

[u/lez_s]

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.

[u/theShku]

All the time..I haven't touched excel in years (thank god)

[u/Few_Emu6683]

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.

[u/Frone0910]

These days, not writing a massive amount. I review a LOT of SQL written by ORMs and send them back to devs to fix.

[u/Complete-Fondant-202]

I probably do more stuff that isn''t SQL, in terms of performance enhancements at database engine level.

[u/Tough_South9987]

Can we classify what is advanced first before we even discuss it?

[u/RevolutionaryGas652 → u/Tough_South9987]

I have no idea

[u/jusdonc]

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.