r/learnSQL • • 4d ago

Keep hearing AI can write good SQL. Can AI write complex SQL involving Window functions, CTE etc for complex business purposes?

19 Upvotes

38 comments sorted by

27

u/Muted_Meaning3754 4d ago

It can do this incredibly easily. Ai can write sql better than just about anyone, it just can't know business value and what to pull without extensive training on your DB

-5

u/Bofact 4d ago

And which user is giving access to their data to an AI?

11

u/AppleBananaFlop 4d ago edited 4d ago

You don't need to give it access to your data to write SQL.
It needs the description of each table and column (name, length, type etc.) and the meaning of the table and columns (semantics). It's good to have all the referential keys, dependencies and constraints and lineage.
Furthermore you should describe in good detail what you need.
And it writes rather good SQL. If you copy that and execute it by yourself your AI never sees the data itself. But it is not an Agent....
But once you got all of this it can write the queries, document and all other things quite fast.
However - I 've never seen a database documented to that extend -- not even close.

12

u/disposepriority 4d ago

CTEs are not particularly complex SQL, regardless I'm quite pleased by AI's SQL work when it has access to all relevant DDL files

11

u/Atmosck 4d ago

AI understands SQL as a language just fine. The challenge is giving it enough context about database for it it to write the right query.

1

u/Altruistic_Demand_11 4d ago

Te refieres al reto de documentar las tablas / categorías / y que la IA tenga a la mano el background de la empresa y operaciones?

3

u/ClearBreakfast2308 4d ago

Here's an example for you. The business might be interested in material return rate differences amongst their sales territores.. to calculate this return rate what do you do; for instance - If an order is cancelled after shipping do you want to include that in your consideration? Do you want to include in the denominator auto-restocking to distributors (these orders might have some exclusion from returns contractually that the DB does not document)?

1

u/ParkingOven007 4d ago

That AND how the application actually uses data and what things mean. Not just structural, but business value.

4

u/MrLyttleG 4d ago

Si tu maîtrises le fonctionnel et que tu connais les structures de tes tables alors la réponse est oui, tu seras en mesure de donner le bon prompt

3

u/DMReader 4d ago

This the answer. How much context you can give it will determine how well it can write that SQL.
My determination for when to use Claude is more about is it easier to explain than it is to do?

3

u/Spivonious1 4d ago

In the time it takes to build up context and tweak what AI gives me, I could have just written the query.

1

u/iAMguppy 4d ago

Yes, it is incredibly effective. I can do in two hours what used to take two weeks.

As others have said, it is more about what it knows about your database rather than its raw ability to write the sql.

It can also write some probing queries against your database that can be recursively fed back in. Just have to start somewhere. Probing queries have helped me uncover all sorts of issues related to data migration.

2

u/Proof_Escape_2333 4d ago

what is the point of learning sql if AI can do a really good job?

1

u/iAMguppy 4d ago

Because “change the break pads of that car” is a more effective sentence than “go fix that thing with wheels.”

Largely may be unnecessary these days, but knowing what to ask for will most certainly yield better results and more efficiency.

You may also be the barrier between deploying something incorrect by virtue of understanding what you’re looking at.

This is not to say that you should be an expert, but generally speaking if you’re looking at some sql, you should at a minimum be able to get a sense of what it is doing, because you may not have AI access or you may have to speak to it someday.

1

u/Bofact 4d ago

Then why having humans as employees and not replace them with AI? For business mans, your arguments are irrelevant.

1

u/jshine13371 4d ago

AI is a better doctor than someone who isn't a doctor. A doctor is better than AI at being a doctor. Which one would you trust for open heart surgery?

1

u/Mobile-Analyst-9617 4d ago

Yep. You just have to give it enough info about the underlying data and business rules

1

u/theungod 4d ago

I just had ai engineer a data model considering of over 100 views, tables, tasks etc with only 3 prompts. Yes, it can write complex SQL extremely well.

1

u/orz-_-orz 4d ago

To me , not so much

In many use case, I can write a decent amount of spec/prompt and get a decent function (API/backend job) I want in Python.

But I have to write a rather detail prompt/ spec when asking Gen AI to generate a workable SQL (to read the right data and produce the right output), at this point I might as well write my own SQL

GenAI is good in making my SQL more efficient though

1

u/Alpizzle 4d ago

SQL is incredibly simple, syntatically. There are about 350 reserved words or so, which is relatively small. The problem with using AI to build SQL statements is they will be syntactically correct, but potentially return the wrong results. There is no substitute for understanding your data and domain knowledge.

The advice I always give regarding AI is to treat it like an intern. It's good for doing grunt work, but as a professional who is responsible for the output, you need to check anything it gives you. I work in the legal vertical in the US, and there have been many cases of attorneys submitting AI garbage. If a clerk provided it to them and they submitted it without review, they would be admonished and brought before the states Bar. AI should be no different.

1

u/DataFracking 4d ago

here's what you do. come up with a smaller problem you're trying to solve, first create files with all the sample SQL with comments in the code and clear descriptions explaining what each one does. come up with a very detailed business problem statement. next what you want to do is for the tables that are pertinent for the query get all example ddl that's associated with them, that means the create table statements, the indexes, the views, any constraints, and any stored procedures, triggers, etc associated with this area. put them all in separate files, have a master file that has a short description of every ddl object, this file should be referenced from your main business context and prompt for the situation. next what I do is I will export the top 50,000 most recent rows from the table and throw them into parquet files and name the parquet file for the table in some cases I even will do up to 400,000 rows or so. I put these in a data context folder for the AI. finally I start the AI CLI agent which has access to read all these files and I give it brief instructions about the folder structure, the parquet files and what they are, the file that has the business problem, the files that have the sample good known queries, this is really the key you have to give it everything under the sun so it has full understanding, then tell it what you want it to do and it will give you some of the best queries you've ever seen if you give it just a substantial amount of context and examples to draw from. What mine does is actually on the fly creates a docker image of SQL Server and it will throw the data from the parquet files into tables for it to query directly so it tests all different scenarios. The trick is to do it from a command line interface AI tool, not a chatbot. Claude code, antigravity CLI, codex, opencode with a decent model can ask do this quite well. It needs to be able to read all the files, it needs to be able to do python testing scenarios, so have python already installed, I would recommend installing docker and telling it that you have docker or podman installed and that it can use it. lol, I went a little overboard with that explanation but trust me it works

1

u/Quesozapatos5000 4d ago

Giving AI the correct context is the real lift here. The code is easy.

1

u/Slitty_sam 3d ago

It can, but in like more than 90% of instances I can just write a query myself faster than I can type out all the explanation to an ai. Ai is still good though for validating your query in terms of giving it a one and asking it "my end result is needs to be blah blah blah, does this function as intended?"

1

u/rahul03rk 3d ago

Short answer: yes, the syntax is the easy part. Current models write CTEs, window functions, recursive queries, etc. without much trouble. The problem is that complex queries fail quietly. They run, return a nice-looking number, and the number is wrong.

The usual ways it goes wrong:

  1. Grain. Joining orders to order_items (or any one-to-many table) before aggregating multiplies rows, so SUM(order_total) comes out inflated. No error, just a bigger number.

  2. Window frames. SUM(x) OVER (ORDER BY day) without an explicit frame defaults to RANGE, so rows that tie on day get the same running total. Usually you wanted ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  3. Ties. ROW_NUMBER() to pick "the latest record" picks an arbitrary one when two rows share a timestamp. You may want a tiebreaker column or RANK().

  4. NULLs. AVG ignores them, COUNT(col) skips them, NOT IN with a NULL in the subquery returns nothing.

  5. Business definitions. "Active customer", "revenue", "churn" mean something specific at your company (refunds? cancelled orders? soft-deleted rows? test accounts?). The model doesn't know, so it guesses, and the guess looks reasonable.

If you do use it, what helps:

- Give it the table grain ("one row per order item") and your definitions, not just the column names.
- Ask it to list its assumptions before the SQL, then check them.
- Run each CTE on its own and check the row count. If a step suddenly has more rows than it should, there's your fan-out.
- Check the final result against a number you already trust (last month's report, a dashboard total).

So it can write the query, but you still need to know enough SQL to check it. That's why learning window functions and CTEs properly is still worth it.

Disclosure, since it's directly related: I'm building FactQL, so I'm biased.

This exact question is why I started it. "Can AI write complex SQL?" turned out to be the wrong question. It can. The real question is "can I trust the number it gives me?", and with window functions, multi-step CTEs and business definitions, that's where things quietly go wrong: a join that inflates totals, a frame that miscounts ties, a definition the model guessed.

FactQL is an engine built for that gap. You ask a question about your data and get the answer back with the query that produced it, the rows behind it, and where each number came from, so you can check the result the way I described above instead of taking it on faith. And if it can't back an answer, it tells you so rather than handing you a confident wrong number.

No link, not trying to sell anything here. Everything above about checking grain, frames and definitions applies whatever tool you use.

1

u/TypicalRule3974 3d ago

For complex analytical tasks and for dirty databases probably not.

For clean databases and the business knowledge isn't so ambiguous, then yep.

1

u/Thriven 3d ago

I do use AI for changes to massive queries.

Most of the time it will be to add new columns to the source CTEs and feed them through to the final output. I'll ask for a completely new file with the edits and then in Beyond Compare I'll compare the code changes to the original. Saves me a ton of time. I'll ask it to notate the lines it changed and write comments for the comment block.

Another use I have for it is creating boiler plate procedure and function code in postgres I struggle with the $$ and BEGIN END. I'll have it write that for me and I'll fill the code inside.

It's definitely worth learning CTEs and Window functions. I would just pair program with AI and ask it to help you write what you want and then you construct the actual query in your editor. When you hit errors have it help you correct it them. That's how you use AI to learn as you go.

1

u/RadiantWhole2119 3d ago

It’s all about context. The more it has the better. Give it database data and it’ll accomplish whatever your goal is.

1

u/joker_face27 3d ago

When evaluating an AI's ability to write code, it often achieves noticeably higher "guess precision" (accuracy) with SQL than with general-purpose programming languages like Python or JavaScript. On academic benchmarks, frontier AI models frequently score between 85% and 91% in Text-to-SQL tasks. In languages like Python, developers must write step-by-step procedural instructions on how to execute a task (defining loops, managing memory, declaring variables). SQL is declarative-you simply describe what result you want (e.g., SELECT revenue FROM sales), and the database's optimizer figures out the execution path. This gives the AI a much narrower, simpler target to generate. SQL is fundamentally rooted in relational algebra and set theory. This mathematical foundation gives the language a strict, predictable, and logical structure. Large Language Models (LLMs) excel at recognizing and replicating strict patterns, making SQL's rigid syntax (SELECT, FROM, WHERE, JOIN) much easier to map than the infinite architectural possibilities of general software engineering.

1

u/ThreeBricksWish 3d ago

It gets the syntax right. Where it slips is meaning. Things like the wrong grain (joining before aggregating, so rows get double-counted), window frames defaulting to RANGE when you meant ROWS, ties (ROW_NUMBER vs RANK), and NULLs quietly dropping out of averages and comparisons.

What helps:

  • Give it the real DDL plus a few sample rows, and spell out the grain of each table.

  • Have it build one CTE at a time and print a row count after each step. If the count jumps after a join, that's your bug.

  • Keep a known-answer case: a tiny dataset where you can work out the right result by hand.

  • Ask it to explain every window clause back to you. If you can't follow the explanation, don't ship the query.

Agents that can run the query against your data catch more of this than a chat window, because they can check the counts themselves. Full disclosure, I work on Genie at Databricks, so Genie Code is what I use for this.

1

u/Ceero_Bro 2d ago

If you feed it enough schema knowledge and you’re good at putting into context what you want it’s incredible

It actually inherently uses CTEa a lot in my
Experience

Iv gotten to the point where I’ll write the bones of a statement, maybe 60% of it, and I can feed it to AI and some context to finish it off, make it more dynamic, clean it up and write for performance

1

u/Training-Doughnut-63 4d ago

Ai is solving unsolvable math proofs, what do you think?

1

u/Bofact 4d ago

Such as?

1

u/RapidHedgehog 4d ago

What the hell are yall using sql for

0

u/OkPerspective9454 4d ago

AI can write SQL better than anyone

0

u/mad_method_man 4d ago

eh.... it does, provided you know what the heck it did

AI is a tool, and a tool is only as smart as its user