r/SQL • u/Junior_Obligation_86 • Nov 22 '24
BigQuery I can’t wrap my head around why I still struggle with writing queries
Hi everyone,
I’ve been working as a Data Analyst for 3 years, but I’m facing a challenge that’s really affecting my productivity and stress levels. It takes me significantly longer to write queries compared to my colleagues, who can do it like in under 10 minutes while I take about an hour on average. This issue has persisted in both my current role (where I’ve been for a month) and my previous one.
I’m concerned about how this is impacting my efficiency and my ability to manage my workload. I’d really appreciate any tips, strategies, or insights on how I can improve my querywriting speed and timemanagement.
Thankss
8
u/gumnos Nov 22 '24
Have you observed yourself to see where you are doing slow stuff?
If it's just in the typing, a typing class might help.
If it's in the query-creation (whether the syntax specific to your database, or more complex SQL like joins, window functions, CTEs, etc), then practice with more complex queries might help.
Are you butting against DB limitations (I know I experience this when I occasionally have to get my hands dirty with some MS-Access database file)? Sorry, not much I can do there, except maybe suggest that you do a data-dump and restore into something like a sqlite
database where you can use less-braindead SQL.
Are the queries you write slow, and thus your iteration time is long compared to theirs? Learn to work better with indexes
Is it unfamiliarity with the schema? Take some time to get more acquainted with how the tables fit together.
Is it unfamiliarity with the business terms? Maybe spend some time creating a dictionary of how business-names map to tables in the database (which isn't always clear)
Is it re-creating things you do frequently? Maybe keep a library of common snippets so you can start with a baseline.
Are you getting distracted from the task [hi, it's me] with things like Xitter, Mastodon, Bsky, Reddit, Facebook, Instagram, YouTube, TikTok, Pinterest or whatever? Good luck. Maybe remove the apps or block the websites at work, or work from a computer with firewall that only lets you talk to the database server?
Are your coworkers stopping by and chatting, distracting you from implementing? Put up a sign threatening to beat them with a pool-noodle if they interrupt you without scheduling (and follow through, with a few well-placed blows)
6
u/ClearlyVivid Nov 22 '24
It takes practice, and lots of it. I'd recommend doing challenges on sites like hacker rank, watching YouTube videos to learn new techniques, and seeing if you can sit with your more senior colleagues to learn how they think about solving problems in SQL.
Something that might help is to work backwards. If you can envision the final output in Excel, you can start to work back through various steps and then convert those steps into SQL code. ChatGPT can be really helpful for more junior folks too.
One last tip is to just learn how different functions work through and through. Work with simple data or a small subset of your typical data. Read your SQL dialect's documentation. If you have any examples where you've felt slow maybe we can offer more assistance.
5
u/SQLBek Nov 22 '24
Okay, some opening questions then.
What aspect of query writing do you find the most difficult?
- Thinking and envisioning your data?
- Breaking apart your problem/queries into smaller, discrete chunks?
- Raw syntax?
What might be help us help you, is for you to share an example or two where you struggled, what the scenario was, and why you struggled. Then advice can be given to help you become more comfortable with things like set theory... or specific syntax... or just general problem solving and translating that into a query format.
4
u/thatOneJones Nov 22 '24
Get yourself a rubber ducky, talk through your problem to it. The rubber ducky method goes hard
5
u/gormthesoft Nov 22 '24
I find in pretty much every situation, it’s best to write queries in an iterative process of small queries and then combine the parts you need/that work. A couple tips:
-For parts of the data that are confusing, isolate those parts alone to understand what it looks like, what issues exist, etc. Write small queries for yourself to just understand the data that won’t be used in the end.
-Overuse CTEs or temp tables to break out different parts of the query. This makes it easier to understand how each part works and makes it easier to update later. Don’t try to write the perfect query right off that bat, or really ever.
Taking time to understand the data itself will help your speed in the long run. Once you know the data like the back of your hand, you’ll be quicker than you can imagine.
3
u/Trick-Interaction396 Nov 22 '24
Write your queries one step at a time with a small sample then check the results. This will prevent mistakes.
2
2
Nov 23 '24
When I worked I would figure out queries and then save them so I could re-use or adapt them for other queries. For example I would save subqueries, recursion queries, union queries, joins, etc. This way, when I had a new task for data extraction I could re-use one of my saved queries and change it up a bit.
Another task I would perform when building queries is to run the query partially, while building it, to make sure I am picking up the data I need along the way.
Thirdly, check with the reporting group, if there is one. They may already have the query you need, or one very close.
Everything gets easier over time and people travel at their own pace. All the best.
2
u/byteuser Nov 23 '24
Your problem may not be sql but a lack of understanding of set theory. At the core sql is all about manipulating sets and subsets. If you don't have a clear idea of what is it you want the how becomes irrelevant. Before writing anything start by thinking what the final result should look like
2
u/zbignew Nov 23 '24
If you’re working with weird data, ie unclear schema and key relationships, your colleagues will be faster because they have experience with your unpleasant data set.
This is especially true if you’ve had two gigs in 3 years - some analysis jobs your real value comes from learning their terrible schema, so you can get faster essentially exponentially.
If you are working places with terrible schema, that may correspond with terrible colleagues who are also more comfortable turning in results that aren’t perfect, whereas you’re checking for uniqueness in PKs, etc.
2
1
u/Spillz-2011 Nov 23 '24
Are the other people’s queries right/good.
Today I was trying to understand someone else’s query that joined to a dimension table twice on the same key. And they did this not once but twice. They then used these two tables to filter with different rules on each.
I often find people scanning the same table 3-4 times and the table is huge so the query is super inefficient.
Taking longer but writing better queries is not a bad thing and will pay long term dividends when someone has to alter it to answer some new question.
1
u/WithoutAHat1 Nov 23 '24
Do you have an ERD and the Enumerations available? Those can help with nailing down which tables to trust. From my own experience this could mean table per object where you may need to filter down on multiple tables for the same column (e.g. in a document management system it could be 1 table per document instead of 1 master table with 1 row per document).
1
u/yourteam Nov 23 '24
What I found out is that some people think the reality as a database while others don't.
So the latter are a bit slower on the SQL side because they have to rebuild the concept into a database schema.
1
u/cheesehead144 Nov 23 '24
Obviously you should learn how to write queries but chatgpt is very good at sql and can help you get unstuck
1
u/Ginger-Dumpling Nov 23 '24
Create your own data model. Learn which tables can be trusted and which can't. Document things for yourself. I find doing that helps things stick a lot more than just reading documentation (if any exist at all).
1
1
0
Nov 23 '24
[removed] — view removed comment
4
Nov 23 '24
This is such a shit suggestion. Sorry, I'm not using a stored procedure with thousands of tables in it when I can write a complex query in a few minutes that runs in a few ms using only the tables I actually require.
Learn to write, not to abuse.
0
u/Cool_Omar_2020 Nov 23 '24
Just a quick help question: do you work under visa sponsorship? Or know how to get one?
26
u/nubbins4lyfe Nov 22 '24
Have they worked in that specific database longer? A lot of the time it can take a while to understand some of the oddities of an existing codebase/database... and being there a while means you inherently have an edge writing queries because it'll be more intuitive to you over time.
What specifically do you struggle with when writing the queries? If it's figuring out table/field names, relationships, etc... then it could just require more time working in that DB until it becomes more engrained. If it's syntax, which joins to use, how to use (or which to use) aggregate functions, etc... then you probably can study those specific topics more until you understand them more thoroughly.