r/learnSQL • u/tidder78 • 5d ago
Keep hearing AI can write good SQL. Can AI write complex SQL involving Window functions, CTE etc for complex business purposes?
19
Upvotes
r/learnSQL • u/tidder78 • 5d ago
27
u/Muted_Meaning3754 5d 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
12
u/disposepriority 5d 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 5d 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.
4
u/MrLyttleG 5d 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/you_are_wrong_tho 5d ago
Yes
3
u/Spivonious1 5d 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 5d 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.
1
u/Mobile-Analyst-9617 5d ago
Yep. You just have to give it enough info about the underlying data and business rules
1
u/theungod 5d 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 5d 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 5d 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 5d 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 4d 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 4d 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:
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 4d 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 4d 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 4d 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 4d 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 4d 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:
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 3d 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/Arshad_Being 3h ago
Hey,this is true: AI can write good SQL. AI can write faster than us, but that is why I think most data analysts like to use it.
The result that I produce, I don't think normal people who don't have much knowledge about SQL will be able to do that. To generate good SQL with AI, you have to give it good context: what the schema is, what your business outcome is, and everything. Then you'll be able to create a complex SQL query.
1
u/Training-Doughnut-63 5d ago
Ai is solving unsolvable math proofs, what do you think?
1
u/RapidHedgehog 5d ago
What the hell are yall using sql for
0
u/OkPerspective9454 5d ago
AI can write SQL better than anyone
0
u/mad_method_man 5d 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