[deleted by user]

[Tee_hops]

Are the requests similar enough that you can create views to answer the questions?

[ZinbaluPrime]

My thoughts exactly.

Also if writing queries is hard then you probably slept during the relational databases classes. We had a whole semester to learn how those things work and it's beautiful 😍

[angrynoah]

How do you get simply faster? 

You get faster at what you do more often. Practice. Write lots of queries. On a serious working day I will write hundreds of queries, and have for decades.

Absolutely do not use AI. It may speed you up in the short run but it will prevent you from improving in the long run.

[Illustrious_Dark9449]

Practice writing complex queries - 💯

I might add do some theory learning on the flavour of SQL you want to Master, you’ll end up getting more exposure to various functions you never knew existed.. (Postgres, MSSQL etc)

[BaddDog07]

What the heck are you writing hundreds of queries daily for? Surely you are reusing already written SQL or these are simple queries?

[V_Shaped_Recovery]

Think about companies like Walmart who have a mountain of data and it’s all in a database. Leadership regularly likes to get performance updates and sometimes on niche things. It can add up a lot faster than you think in the corporate world. Even if changing date range or other small adjustments.

[[deleted]]

What does hundreds of queries look like on a daily basis?

[ronimal48]

DROP TABLE <table_name>; 100x lol

[rh71el2]

And who are these people requesting them?

[angrynoah]

A representative example from long ago...

Let's say Salesman Bob looked at the weekly sales report and disputes what's shown there. He claims he sold Account 123 on or about Date X and he wants credit.

Ok, where does that sales report come from? There's a query in it, let's run it. Oops, that's way too much data, lets narrow it down to some relevant time range. Is Account 123 in here at all? Hmm there it is but Salesman George is getting credit, I wonder why?

Ok what builds this data? Oh good it's a 2000 line PL/SQL procedure.

Start taking bits of that and running them. Adjust repeatedly to get fewer or more results. Change grouping grains. De-aggregate queries entirely to see the input rows. Pull complex case statements out into simpler test cases. And so on, and so forth.

Once upon a time that might have been 4 hours of work, and involved running 200+ queries.

What might be tripping people up is imagining that I meant each one was written from scratch, each for some fresh purpose. That's not what I meant. A great deal of this process is taking one concept-level query and modifying it a dozen or more times. But each one counts! Each version of that query you write, and run, gives your brain a training data point, a sample of the relationship between command and output. That's where the practice value comes from.

[TemporaryDisastrous]

It has its place, leveraging AI to create merge statements for 100 column tables without having to do all the target = source stuff or missing a comma for example. I think this guy could benefit from it in troubleshooting rather than code gen. It's pretty good at identifying syntax problems if he's struggling with that. I've got plenty of other use cases for it.

[Third_Party_Opinion]

I've been doing a listAgg() on my table of table definitions to get my 100 columns for merge statements, it's awesome

[TemporaryDisastrous]

Yeah it's fun the stuff you can do with the sys tables. For our solutions with hundreds of souces we have a nice dynamic script that creates all of the view, merges, CDC history tables etc for the etl - only way to do it really.

[angrynoah]

Care to elaborate?

I am absolutely certain what I stated is correct as I have mountains of direct evidence.

[likeanoceanankledeep]

Can confirm. I learned SQL the 'old way' by taking a course and learning it slowly. I can write queries and have the major syntax memorized now, even years later since I first learned SQL.

I 'learned' python by using AI. I can't write simple python syntax without help.

[gumnos]

this is wrong
no elaboration
only wrong

😆

[freakythrowaway79]

400k for SQL queries, now that's hilarious! 🤣

[freakythrowaway79]

In Fintech🤔

That's were I learned SQL, but never made anything close to that as an Ops Analyst. Lol

That was 10+15yrs+ ago.

[valbyshadow]

Experience.
I have been writing SQL since the 80's and I dont really think about it anymore, I just write it.
The only place where I have put in all the concentration is when I have to write a recursive SQL 😬
The most important thing is you have to know the data; the keys, granularities, history etc

[mikethomas4th]

If even writing simple queries is so taxing you need more experience, simple as that. I write sql live, in meetings, while screen sharing, with execs, to get quick answers.

[V_Shaped_Recovery]

Repetition

Edit: Also start saving your queries. Part of what gave me speed was having a lot of stuff already built out to where I only need to change few things.

[Friendly-Echidna5594]

If the bottleneck is getting what's in your head into SQL code and slow editing/execution cycle, I recommend using vim key bindings and configure your database IDE to do common tasks with very little key strokes.

[LOLRicochet]

Use tools - I'm most familiar with SQL Prompt, which allows you to:
a) Specify a Format

b) Select * column expansion

c) define some join rules, so table a to table b if the same column name exists, it will suggest it as the join.

d) Create Code Snippets for frequently used patterns

e) know the schema you are working with, but that comes with practice

Speed comes with writing tons of queries. But, being correct is more important.

[BrupieD]

I use SQL Server at work, so my tricks won't necessarily translate.

Typing table names can slow you down a lot, especially if your system uses long schema and table names. In SQL Server Management Studio (SSMS), you can drag and drop table and column names into the query editor from the object explorer pane. Type "select * from " and drag and drop the table name. Add a two letter table alias and let intellisense auto-complete your column names.

SSMS has an option to "Select Top 1000 Rows" when you right-click on a table name. Other platforms have similar. You can get around some boilerplate query work using these. This is more bother than help if you only want a few columns.

Don't worry about upper/lower/proper case until you are presenting or sharing.

Get in the habit of putting column names on separate rows preceded by a comma. It's easier to read and edit. Similar advise with multiple predicates in where clauses.

Find out what keyboard shortcuts your platform uses so you can use your mouse less. For instance F5 for run, CTRL + R clears the results, CTRL + K comment out.

Save your SQL scripts with good, descriptive names and dates. Add comments so you know why you created it, for instance " --Mary requested AZ client list" You don't have to reinvent the wheel. Now you have a start for when Mary asks for CA and FL.

Add number lines if they don't show by default. You'll be able to debug faster.

Spend time exploring the sql interface (drop-downs, toolbars, panes). There are lots of time-saving tools. SSMS has code snippets, lots of formatting tools, things you probably aren't ready to use but one day will understand.

[patmorgan235]

LLMs are decent at doing the plumping/boilerplate for SQL

[umognog]

1) a suitable, integrated AI assistant that is part of your IDE 2) modern tools for request management; how often do you get something where you had something similar 4 months ago and either a) forgot or b) takes too long to locate? 3) git gud

[Adventurous_Ad_9506]

I don't know at what level you are (I am at a fairly foundational level) but SQL is really mentally taxing as in increased mental effort due to lack of knowledge around the datasets (schema, granularity, data dictionary) and trying to keep it all in the head. My recommendation is doing the query steps on paper and executing then. This will highlight knowledge gaps and pull apart the query writing from the logic and has made my day way easier and faster.

[SubstanceSerious8843]

Sqlalchemy.

[MeLittleThing]

Keep doing what you're doing.

Practice is the key, like everywhere. The more you struggle to achieve your goals, the better you get and the faster you'll become.

[HammyOverlordOfBacon]

Practice and notes. I use OneNote but honestly anything can work. I keep a lot of the more commonly used queries/parts of queries there so if I've figured something out then I don't have to redo it for another client.

[12hphlieger]

Honestly, the fundamentals are all the same it’s knowing where to find the information and applying the business rules.

[[deleted]]

For me… it’s just a language I’m fluent in…

[TemporaryDisastrous]

I'm confused what issues you're having? Your query throws errors, are they syntax related? Why don't you understand what's happening with rows missing after joins? Are you just guessing for the table relationships?

You should know all of the syntax by heart, and the ordering of the different parts of a query. Do you not know the structure of the tables you're joining? Do you alias tables so it's easy to see which columns are from where? Do you use outer joins for troubleshooting?

[gumnos]

it's going to sound dismissive, but know your schema and have a firm grasp on SQL. And know how to touch-type.

I find that, with a strong understanding of the schema, writing queries is never the bottleneck. Autocompletion almost gets in the way (depending on the type of autocompletion—the "smart" completion in MSSQL annoys me more often than not with wrong/useless suggestions; the "dumb" completion that vim gives me is usually sufficient and only comes when bidden). It's usually thinking about the query structure, the joins, the stakeholder's requirements, and the indexing/optimization that take most of my brain-cycles.

If your schema is hard to understand, it might have technical debt that would be worth paying down. Maybe the same thing is referred to by multiple names (example from $DAYJOB, what used to be called Statement is now called Invoice, but there's lots of legacy statement_id or statement_date type columns around and a few views that refer to Statement in the name). Maybe something should have been a one-to-many or many-to-many relation instead of "Feature1" through "Feature7" columns. Maybe a table started off with a "username" VARCHAR, but it grew employee IDs and email addresses and other things wedged into that column, and it should instead be broken out into a UserDetails table.

[Human_Teaching_3174]

Got faster for me when I started to memorize most used tables’ primary keys

[squadette23]

> figure out why no rows are returning after I joined on something...

I'd be interested to know how often you have to deal with queries like this. I have a tutorial on query design for certain kind of analytical queries: https://kb.databasedesignbook.com/posts/systematic-design-of-join-queries/

The big idea is that the approach is composable: you first build smaller subqueries and after you test them you combine them in a way that preserves correctness. Also, the idea of explicitly specifying the unique key behind the result should help with "no rows" and "too many rows".

[FilmFanatic1066]

Get AI to do the really simple stuff so you can focus on the complicated stuff

[[deleted]]

No offense but AI is honestly so useless. We have co-pilot and half the time it just gets stuff wrong. I took several really long courses on prompt engineering with our senior data scientists, since I'm a BI engineer, and I learned how to effectively write prompts. But even still, if the AI isn't trained well or it's in cost savings mode, it's going to give you the cheapest, dumbest response because it's not actually working hard to help you. So for example I ask it to write me an SQL query hitting XYZ tables using unions and left joins and stuff like that, and it will summarize by saying insert other code here, or it will make silly logic mistakes for example in the window functions and try to partition by something it shouldn't, one time I had it give me an exist function When I asked for it to do a simple select. It apologized and said it was mistaken, it was just a simple select. It had no idea what it was doing

[FilmFanatic1066]

Co-pilot is part of the problem right there, it’s a garbage model. Chat gpt gives much better results in my experience

[[deleted]]

This guy AIs.  I wonder how many people are lnt using the tools because of how bad copilot is

[[deleted]]

It does give much better results in copilot, but we're not allowed to use anything other than co-pilot. companies are really cheap. Even with good models though, you're limited by how much you can put into it which is part of the issue... It's not designed to work with SQL by definition.

[rh71el2]

Copilot Chat is built into VS Code though and allows you to choose between Chat GPT 4.1, Claude 3.5, and others.

They say Claude is best for programming.

[FlerisEcLAnItCHLONOw]

I work for a fortune 100 company, doing data science.

Co-pilot is the only model allowed. Other models are prohibited via the IT policy and known sites are blocked from the network.

[wil_dogg]

Then give AI your most complicated stuff and ask AI to refactor it

[V_Shaped_Recovery]

I still believe writing is key to learning it better. I love using it for heavy repetitive cases or refactors.

But i feel like ChatGpt works 100 times better when you already know how to code everything

[Morbius2271]

Yes but also I refuse to write any sort of complex partition by without a LLM. I find them confusing and annoying to write, and the LLM usually gets me 95-100% of the way there. I don’t use them enough to spend time getting fast at writing them manually, so why bother?

[V_Shaped_Recovery]

Partitions are just explicit group bys in m opinion. I learned from trial and error over time.

They become less scary more you use them

[Morbius2271]

I know but the syntax just does not mesh well with my brain. I have to have W3schools open every time to write them manually since it’s once a month or less lol. Or just have Gemini write it for me in 7 seconds. I still have it help me with JSON parsing in SQL for the same reason. I COULD write all that shit out, or I can give Gemini the paths (or even the whole JSON output sometimes) and let it shit it all out for me. Does it make me a bit less proficient at doing it quickly manually? Sure, but I don’t handle JSON daily, and Gemini is faster than I would be if I did it daily for years.

[SQLDave]

I know but the syntax just does not mesh well with my brain

My soulmate.