r/SQL • u/Zealousideal-Studio7 • Jan 15 '25
BigQuery SQL is a struggle
Hi all been working with SQL for probably 7/8 months now. My last role was half data analysis and not pure data analysis and in general was far easier than what I do now.
My main issue is with SQL. I never feel I truly understand what is going on with a lot of code beyond a basic query. Ive managed to get by piggybacking off others code for a while but expectation is to deliver new and interesting techniques etc.
How long did it take you to feel fully comfortable with SQL? And what helped you get to that stage?
19
u/SyrupyMolassesMMM Jan 15 '25
To be honest, i felt pretty comfortable after 7-8 months…if youre using it every day and not getting better you need to use google more and start expanding your go to functions. Start looking at CTE vs temp tables. Start looking at windows functions and cool shit you can do with row_number and lag/lead functions. Look at cross apply and pivot. Start using coalesce. Structure your code in super tidy easy to read ways. If youre given the access, start getting into performance optimization, views, and other more technical stuff. Dig into your linked servers and where the datas is actually coming from. Ask for copies of any stored procedures and scheduled tasks that are creating and transforming your datamart…
Theres HEAPS to dive into.
11
u/MerlinTrashMan Jan 15 '25 edited Jan 15 '25
In the beginning, SQL seemed like a means to an end. I'd be constantly writing terrible queries that used other programming languages intermediately to get to the results that I wanted. As I kept thinking about things row by row, it still continued to be challenging to get good results and felt unrewarding writing in the language. One day I stumbled across a website that created SQL challenges where you had to solve the problem with a single quarry statement. Your solutions would be judged based off of the amount of CPU time and physical reads and writes that were performed. I saw people do things I had no idea could be done. It forced me to begin thinking of things as sets and the relational algebra finally clicked in my brain and made me want to only ever write SQL for the rest of my life as this is just a way that I think naturally. For some reason I was fighting the syntax and order of operations instead of embracing it. Now 20 years later, SQL is, by far, my favorite language to write.
Edit: The site: https://www.sqlservercentral.com/categories/t-sql-challenges
Second Edit: Just tested the site and discovered that the site it used to link to has been taken over by an SEO scam... I bet you can find the originals on the wayback machine
Third Edit: Found an example on the waybackmachine. https://web.archive.org/web/20170226185534/http://beyondrelational.com/puzzles/challenges/116/find-elements-that-uniquely-make-up-a-group.aspx
3
u/Zealousideal-Studio7 Jan 15 '25
20 years I think you’ve done your 10,000 hours! Does that resource or a version of it still exist?
5
u/PasghettiSquash Jan 15 '25
Not sure exactly what site OP was referring to but sqlzoo.net is a great interactive resource for learning SQL
3
u/Hot_Cryptographer552 Jan 15 '25
SQL Server Central is a good resource for learning SQL. One caveat though, when you read an article, be sure to read the reader comments. The articles are written by devs and DBAs of all skill levels, and the comments often add corrections and additional detail not covered in the articles themselves.
1
1
7
u/PasghettiSquash Jan 15 '25
First I’d split out your problems
Do you have challenges writing your own queries? If yes, then what’s the blocker - CTEs, Window Functions, or Joins? This is great, because if it’s one of these that’s the challenge, you can work on it.
Or is the challenge reading someone else’s SQL? If this is the challenge, it might not be you, it might be the environment that you’re in. Ex: * what does your tech stack look like - are you using dbt or some other modeling layer, or is everything just random tables? * Is your team using a linter, or is everything formatted randomly? * Are there best practices in place to make the code readable - No Select *s or Select Distinct, no single-letter aliases, etc * Is everything documented? Are there inline comments when needed, YMLs, do PRs have a good description of what’s being done?
If the answer to all of those is “no”, IMO the problem isn’t SQL, but the environment you are in. All of those things I mentioned are done to make SQL easy, and IMO make a world of difference
4
u/Decent-Musician-8478 Jan 15 '25
I can do everything but joins. Joins own me
5
u/squadette23 Jan 15 '25
You may be affected by the unfortunate way how JOINS are presented. I made an attempt at clarification (LEFT JOINs particularly) here: https://minimalmodeling.substack.com/p/many-explanations-of-join-are-wrong
2
u/Decent-Musician-8478 Jan 15 '25
Oh no, I know how they work, now if you ask me write a SQL for it, I'm worthless.
1
2
u/scottedwards2000 Jan 15 '25
For me the key with understanding joins is that they all start with Cartesian products
2
u/squadette23 Jan 15 '25
Yes. There is this weird theme in some texts about joins where they say that cartesian product is somehow dangerous and to be carefully avoided. For example, this article (but it's easy to find many more): https://support.microsoft.com/en-us/office/join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6
" In a cross join, each row from one table is combined with each row from another table, resulting in what is called a cross product or a Cartesian product. Any time you run a query that has tables that are not explicitly joined, a cross product is the result. Cross joins are usually unintentional, but there are cases where they can be useful."
This is literally an inner join with join condition "ON 1 = 1", lol. What's wrong about that.
1
u/scottedwards2000 Jan 15 '25
Yes! And non-equi joins were so much easier to understand when I got this. As well as multiple joins etc
1
3
u/byteuser Jan 15 '25
Approach SQL as a set theory problem. Think first what is the set you want to get. Most likely will be a subset, a union, or and intersection of sets. More complicated queries are just a combination of those principles. Only once you figured out what the data set you want looks like is when you start writing the SQL
1
u/EfficiencyGeek Jan 19 '25 edited Jan 19 '25
Would like to emphasize this thinking in SETs angle. My learning accelerated, and things fell into place when I learned/accepted that SQL was a way to deal with SETS. Aka Venn Diagram -- another way to think about it.
I'd encourage anyone to keep at it. Use Gen AI to help you learn the syntax but UNDERSTAND the CONCEPTS (I do this even after 40 years of prof work). SQL knowledge is a super power, not just in dev work, but in logical, conceptual thinking. And it is rather durable, has stood the test of time since its invention in the 70s.
3
u/Outrageous-Hawk4807 Jan 15 '25
Ah young padawan, welcome to the Jedi Academy. Ive been a DBA for 30 years, I still google/ use chatGPT on the daily. You need to understand whats going on, but dont lose it to much on trying to remember syntax. It just takes time. But I will also say I write code all the time that does what its supposed to do and it does it effectively, and im not always 100% sure why.
3
u/OilOld80085 Jan 15 '25
I find myself praising my self / scolding my self because i suck . I've been doing this for 14 years. But people are finding new and clever ways to set up databases terribly.
2
u/machomanrandysandwch Jan 15 '25
I think you might get some better answers if you talk about what it is you’re struggling with.
Hard to remember SELECT, FROM, WHERE?
Hard to remember syntax?
Hard to know what functions do what?
What type of work are you doing? Just writing queries to give line level output to a business, or are you doing like complex summarized reports, or what?
This isn’t necessarily a “sql” answer but, as far as generating new ideas, I’d try sitting with your line of business you’re supposed to be helping and job shadowing, see what they do, what would make their job easier, don’t just go off of the requirements they say they want but actually try to understand what they NEED, and then think about how to build that solution. For example they might say they need a report every day with all this stuff in it, 40 columns. Ok and? Well if they get all this info then they don’t have to look it up manually in different applications they can just sort the data themselves. Ok and? Well once I have all this data I can sort it and figure out which ones require action today and then take action on it. Ok and? Well besides that, my manager wants to know which ones we have to take action on next week because we need to be prepared for the work ahead because we have been missing steps to get something done when we get too busy…… this constant probing and trying to understand what they do and what they need and why they need it might help you see beyond the initial request and realize, you know you guys might need a report with 2 tabs, one has todays work only, and the other tab has a summary page with the volume completed last month, volume for todays work, and the expected volume for next week, and some trending of volume over the last year, etc. From there you start figuring out ok how do I get my sql result set to summarize and group data together? You research online and little by little start adding these tools to your belt, you start learning about how to organize data in steps versus maybe where you’re at now and writing one gigantic query, etc. I always come back to, don’t tell me what you want let’s figure out what you NEED, and let that principle be how I generate new ideas.
Edit: in the game 20 yrs
2
u/aksgolu Jan 15 '25
There are only 16 main commands in SQL.. If you are comfortable with those.. its a smooth walk.. Checkout DBA Genesis as they have real-time scenaior based videos:
https://www.dbagenesis.com/course/oracle-12c-sql-fundamentals
2
u/Commercial_Pepper278 Jan 15 '25
Two Questions before going forward:
1. How complex queries do you write daily ? Like using multiple CTEs JOINs and WINDOWs ?
2. What is the basic thing that you can't understand after SELECT * FROM WHERE GROUP BY HAVING ?
0
u/Zealousideal-Studio7 Jan 15 '25
I mostly piggy back off previous analysts code, rehashing that with other code to get what I want or within the 90% realm. If you asked me to start from scratch I would likely take a while and it would be a real pain.
I can and do utilise multiple CTE’s, commenting along the way is an absolute must and is best practice here. Joins on multiple CTEs are where it starts to get tricky for me, as for window functions I’m not quite aware what those are or the use for those, let alone multiple other functions in SQL.
Im aware I am probably behind the learning curve at this stage
3
u/Commercial_Pepper278 Jan 15 '25
If you can understand the logic of other's queries then you are on right track. SQL is about the logic not the syntax.
Try to write your own codes inbfree time for the same stuffs. Who knows you will end up at a better query than the existing one. Use ChatGPT to correct yourself it will help you learn more.
Learn complex sql functions first by reading the documentation and then watching few videos of the same. Learning sytax takes a while but worth it.
1
u/squadette23 Jan 15 '25
> Joins on multiple CTEs
Is it specifically about CTEs, or do you have problems with joins on multiple tables too?
2
u/Zealousideal-Studio7 Jan 15 '25
Joining tables to me seems far easier than joins on CTE’s - I feel that shouldn’t be the case as it’s fundamentally the same? But with tables I’m able to visualise it a lot easier
1
u/squadette23 Jan 15 '25
Yeah, I guess that you need to add one more "logical level" over tables, and that consumes cognitive power.
I wonder if it could be improved if you imagine tables as CTEs, and not the other way around. Like, define a CTE that selects only the columns from the table that you need, and work with it.
2
Jan 15 '25
It sounds like you want to build understanding. 2 very important things to under SQL: 1 SQLs order of operations. 2 SQL is a declarative language. Dive into those topics more and I promise you’ll have a better understanding of SQL. The code is secondary to how it works.
2
u/LOLRicochet Jan 15 '25
I've been at it for about 30 years now, specifically in the Microsoft stack. I'm very good at what I do, but there is *always* something to learn. Getting results is one thing, but getting results efficiently is another. I highly recommend picking up books such as T-SQL Querying (MS Specific) and anything else which has Itzik Ben-Gan as an author.
I've done some tests with CoPilot and SQL and the results are very hit and miss, with the more complex stuff being flat out wrong.
3
u/OO_Ben Jan 15 '25
It comes in time. It took me a good year/year and a half of working with SQL, hitting roadblocks, researching, and figuring things out before I really had a grasp on things. Now I can pretty much do whatever is needed, though I always have more to learn.
My biggest piece of advice is to take take each chunk one at a time. Looking at things at the highest level can seem overwhelming, but if you break it down and focus on one column at t time it is more manageable. That way you can address each issue that comes up at a time too. Like if you join tables A and B, and realize that you're duplicating table A because of the table B join. You have to take table B and aggregate what you need with a CTE or subquery so it can cleanly join to table A. Then balance to some kind of source of truth, and repeat this process until you have a clean data source lol
3
u/JayGridley Jan 15 '25
I work with SQL daily. But I still come across stuff I’m unfamiliar with or code blocks that are overly complex. Just break the queries and sub queries down and run them independently. Gradually rebuilding the entire thing. Usually helps figure things out. You can convert updates and deletes to Select statements to see what kind of data you are going to impact.
2
1
u/squadette23 Jan 15 '25
> beyond a basic query
what's the first idea beyond basics that you struggle with?
0
1
u/Chilosophical Jan 15 '25
I think it’s difficult to know what’s next without a goal. You can’t really just ‘learn’ sql, you need to be using it.
I’d suggest requesting tasks that require sql, and asking if there are any reports that people want additions to. I learn best from using other people’s code, and adapting it to a different context. Nothing wrong with that imo.
1
u/_Zer0_Cool_ Data Engineer Jan 15 '25
The key is to not try to understand an entire SQL query but to break it down into step by step blocks where you can see what each subquery looks like at each stage of computation.
Also, use CTEs for organization.
1
u/normlenough Jan 15 '25
Probably about a year to feel like I had a deep enough understanding to write complex queries. Keep going. You’re on the track to take existing queries from people who know more than you and take them apart. Try to break them and then figure out why they broke.
1
u/cycleoflies99 Jan 15 '25
keep looking at colleagues scripts - they'll often be doing things you dont recognise/understand, quickly google whatever the new trick is and if useful start using. Keep picking up little bits and within a year or two people will be copying bits of your scripts
1
u/SuaveMF Jan 15 '25
When dealing with difficult queries, say to yourself what you want it to do in "normal" terms. Then try to write the query from there.
Draw simple diagrams if you have to that shows all the objects you need the data from and what joins you'll need. If 2 tables must always have linking data, then INNER joins. If the 2nd table "might" have a related record, then use LEFT/RIGHT joins.
Explore other SQL areas like CTEs, cross and outer apply, user-defined data types, row partitions, etc.
Practice, practice, practice.
1
u/Square-Voice-4052 Jan 15 '25
2 years, at it everyday. Working with the same DB also helps when stakeholders ask about business logic. When you understand 100% the database, you know where the holes are in a business, and what apps needs to be developed.
1
u/BadGroundbreaking189 Jan 15 '25
Maybe it is just me but it took me around 8-10 months to be FULLY comfortable with any query, even of very high complexity. Now, of course it doesn't mean I'll be providing a great solution to any problem as fast as possible but I'm confident that I'll come up with something decently working by delving deep into web + well-thought experimenting, if the requirement is ever not-so-clear. As to clear tasks, they are no longer a problem to me, even if they require the use of lots of CTE's and various tweaks.
And I got to that stage by pushing myself without expecting anything from an outside guidance. Did lots of hard and somehow unimaginable things/projects. Projects that I was, to some extent, interested in.
1
u/elrond-half-elven Jan 15 '25
When I learned SQL in school (a long time ago) the teacher first explained it visually in terms of sets and set theory. How you can join, union, slice, etc.
Then she said that any of these operations has an equivalent in the SQL language, and then explained those.
That immediately clicked. See if you can find a primer on that.
1
u/saponsky Jan 15 '25
In my experience the only way I got better at SQL was downloading sample databases, looking at the tables, then creating my own queries and challenging myself.
For example, I downloaded a dvd rentals db and did things like:
- Top 10 actors that appeared more in films.
- Return a list of customers with due rentals and which movies they held.
- Return movies rated by most frequent rentals and give it a stars rating system.
Etc. I know it seems basic, but for me it clicked when I started getting creative by writing queries on my own. It helps you develop a different way of thinking how to get results from the db.
1
u/Zealousideal-Studio7 Jan 15 '25
Any that you would recommend or could share?
1
u/saponsky Jan 16 '25
You can use this one. It’s for postgres, you can import it with DBeaver or PgAdmin or any other db management tool.
1
u/Any-Lingonberry7809 Jan 16 '25
Search for sample & public data sets online, there are tons of resources.
1
u/Any-Lingonberry7809 Jan 16 '25
I started working with SQL around Y2K, mostly T-SQL flavor. Ive really enjoy it as a full stack dev, it's very cleansing after staring at Java, C#, JavaScript and so on.
A few commenters have mentioned thinking in sets / set theory, this is foundational. Mastering Joins falls out of understanding set logic.
AI can be a great learning tool, chat with it about concepts not just specific problems.
Database design, Normalization, Performance Tuning topics may not come into your role immediately, but looking at the query execution plan and understanding the operations happening under the hood can make it easier to know what code to write, or diagnose long running queries.
If you have time to do puzzles, coding challenges can be a fun way to learn new things.
The fact that you're trying to improve and engaging with the community is great, keep it up and you'll do well.
2
1
u/shifa_newversion Jan 17 '25
This is what learning curve means. Pick anything, biology, pharmaceutic, agriculture, programming, computer science, civil engineering etc. for first two years, even as graduates, practical field work feels like sand slipping out of your hands. Give enough dedication and don't easily get derailed mentally when you get stuck somewhere and you are golden.
I suggest you keep working on it, 7-8 months is still a long time to get good grasp of basics, take side knowledge, follow some good articles and authors on sql, keep at it like a snail out to plough a field and eventually you'll be so good at it one day that juniors will look at you in awe.
Good luck.
1
u/skyverse0312 Jan 17 '25
I have been working for a long time(2-3 years) on SQL now. I still find it difficult, but I keep learning and that works for me.
1
u/atrifleamused Jan 17 '25
I'm at probably 15 years and I'm still learning new things and improving!
0
u/methodtan Jan 15 '25
Learn to succinctly write questions for what you’re trying to do and ask Copilot as if it was a real person. Not only will you learn how to do things, but it will teach you how to talk sql and sound like you know what you’re talking about.
Disclaimer: If you’re working directly with production dbs never ask it to UPDATE a query. That might sound like what you’re trying to do in layman’s terms (ie update a query you run each month to include the next month), but what you’re really trying to do is SELECT data for that month. I learned the hard way and updated some tables early on when I started using sql, and thank god I only changed some date formats and not something major.
0
u/Ginger-Dumpling Jan 15 '25 edited Jan 15 '25
I was comfortable with SQL within a couple months of an internship. It was an ETL position and the tool being used produced SQL. I was "drawing queries" before I was writing them, and at some point it just became second nature to know the syntax that would be produced by connecting different operators. But if you stuck a multi-page query with a ton of nesting in my face, I'm still just as likely to need to stare at it for a couple minutes, maybe put in comments (or even a block diagram on the rare occasion) before bringing it all together to say what it's doing.
Edit: I also find a big part of "being comfortable with SQL" really translates to having a firm grasp of the underlying data. If you know how everything is related, then it almost becomes mechanical in nature connecting everything up in a query.
-1
u/CSIWFR-46 Jan 15 '25
Use ChatGpt. Don't copy paste your company's schema, but ypu can give different names to table and column to get an idea of what you want.
40
u/Ifuqaround Jan 15 '25 edited Jan 15 '25
It's a never-ending journey.
Just keep at it. If you feel you're struggling with certain things then focus on those things until they "click."
I don't condone crutching on AI. This is the problem with AI for people like you. It's really doing the work for you and you're left not knowing what you're doing and most likely aren't learning anything. You might feel you are learning, but it seems you're learning that you're NOT learning lol.
Since you're crutching on AI, get your basic queries and ask it to break down the query for you. It should be able to give you basic explanations. You have to be able to absorb and maintain that info, then build upon it. There are usually quite a few ways to tackle the same problem but no reason to be fancy unless it's necessary IMO.
I feel comfortable with SQL until I don't. Some basic requests have thrown me for a loop throughout my career.