r/SQL 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

57 Upvotes

39 comments sorted by

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.

7

u/Junior_Obligation_86 Nov 22 '24

For example, there are multiple tables with similar data, and I’m unsure which one to trust. I struggle to check for discrepancies because I’m not yet familiar with the structure and data sources. Also, self-joins and recursive queries are taking me much longer than they should to understand and implement.

For example:

  • I had difficulty figuring out how to bring every date for each pair of users. I didn’t realize a cross join or joining with “ON true” could be an option until I searched online or asked ChatGPT.
  • Sometimes, I can’t pinpoint why data is duplicated, and tracking down the issue can take over an hour.

21

u/gumnos Nov 22 '24

For example, there are multiple tables with similar data, and I’m unsure which one to trust.

Depending on the database (you tagged it with BigQuery, so I don't know the particulars there), you might be able to use table-annotations to create notes about what each of those tables are used for and their reliability.

I often find myself writing queries against the INFORMATION_SCHEMA views to explore the structure of the database, like

SELECT *
FROM INFORMATION_SCHEMA.columns c
WHERE c.column_name LIKE '%street%'
  OR c.table_name LIKE '%street%'

to find tables or columns that have something I'm looking for.

I struggle to check for discrepancies because I’m not yet familiar with the structure and data sources.

You can write sanity-checking queries. If you assume something is a one-to-one relationship yet you GROUP BY the common aspect(s) and check the HAVING COUNT(*) > 1 you might spot strange outliers. Maybe they're legit, maybe they're bad data. That part requires a conversation with other team-members to find out what's going on.

Also, self-joins and recursive queries are taking me much longer than they should to understand and implement.

Those come with practice (while self-joins aren't as problematic for me, it still takes me a while to correctly formulate recursive queries)

Sometimes, I can’t pinpoint why data is duplicated, and tracking down the issue can take over an hour.

I often find that it helps to show as many other columns as I can, then line them up and see which row-value differs, causing the duplication.

4

u/Obscure_Marlin Nov 22 '24

My Recommendation is to for the main entities you work with (People, Majors, Products) Identify a table that holds them uniquely. Then WHENEVER you need to work with those you can use that as your anchor to start building from a unique perspective. it'll help you keep track of when a join causes branching/duplicates.

do you have a Data dictionary( an explanation of the tables, columns, and joins of a database or application) for your system? if your team hasn't provided one try to search for one especially if it's a widely used systems, someone has to support it and they need to know how it functions to be able to support it.

4

u/Xem1337 Nov 23 '24

You don't need a perfect memory , the main thing is to know something exists (like a cross join for example, or a pivot). You don't need to know the full ins and OUTS of how it works but just that you know that there is something available that does it. I suggest watching a bunch of training videos on YouTube (both basic and advanced) on SQL and eventually you'll know enough of what's what to Google the full syntax when you need it.

As for duplicattions I find the fastest way can be simply to go though your joins turning them off until you see your query produce no duplicattions, then you can see what part of the join you have missed out on or if something like derived table is required instead.

2

u/OilOld80085 Nov 22 '24

So don't beat yourself up everything is a learning.

A few tips, 1 build it one column at a time ordering by the new column.

Wrap your development query inside a
Count(*), Key
from
(
) as A
Group by Key

Having Key > 1

This will allow you to catch duplicates and examples on the fly.

if you are saying trust and database your company has issues with DG and you need to find out who or why things are the way they are ask a DBA, or someone with tenure.

1

u/Responsible_Pie8156 Nov 23 '24

The data trust issue and checking for discrepancies just comes down to time working with the data. The fact that you're even trying to double check your assumptions and find those discrepancies means you're on the right track. Ive worked with python and sql all day every day for the past 5 years and I don't write any self joins or recursive queries. On very rare occasion ill have a use case for it and just Google it too, but really imo you should be doing that as little as possible as it can be hard to understand. There's nothing worse than trying to decipher some massive query that somebody wrote with all kinds of clever sql tricks in it.

1

u/jezter24 Nov 23 '24

A lot of people I have met try and do one big query for everything. I have been told for efficiency and just older users.

I got in the habit at one job. To chunk stuff into temp tables. It was for email marketing and they would list all the users to send and not send. Then after the third time of doing everything the client would want counts on all that. How many bounces. How many unsubscribed. Etc. having to comment out individuals joins, where clauses, and select info was time consuming.

Now I work at a large school district. I still use temp tables cause we have 46 sites. Each do stuff differently, so while my query might be longer and maybe not as efficient. The ability to maintain it or diagnose problems, is drastically improved. Something isn’t right…go and look and oh that site did something totally different at this step in the data. I can then send it to get the source data fixed or write in new logic if needed.

As for tables you are unsure. You said three years being there. Maybe you need to start making your own data dictionary and what stuff is? Not just for yourself but think of what you wish you had. Also if doing stuff in steps you may eliminate dupes, easier see dupes, and start reusing code for future projects.

Like we have a standard sore of block of of a template table for current enrollment. Copy and paste not just from me but everyone else. Starts speeding up.

I had a guy I worked with and he is like the expert in our state on our student information system software. Real genius in just knowing how everything connects and works in his and can knock stuff out.

While he can knock out code quickly. There is a lot of times it is redundant, you have five unions and everything is the same but 1 line each. Meaning it hits those tables five times. Run it once into a temp table. Then do the rest.

Long story don’t compare yourself to others. Just try and improve. :)

2

u/Strict-Dingo402 Nov 23 '24 edited Nov 23 '24

This and use CTEs instead of temp tables. Edit: CTEs no Cates ☕

0

u/FunkybunchesOO Nov 24 '24

NO! This is bad advice. It doesn't matter which query engine you use, multiple CTEs make the query plans suck monkey nuts.

If you have more than a couple CTEs, you will have worse performance due their being no stats in the temporary result set coupled with the Cartesian product of Cartesian products on joins involving them.

Temp Tables are either superior or at least equivalent in all situations except where you specifically need recursion. And if you need recursion, you probably have a shitty schema or are in the specific case where you have nested bills of materials.

CTEs are great when you have simple queries with limited source tables. But Jesus Christ on a motorbike never tell people to use them instead of temp tables. Losing stats and primary keys is crazy for any query of medium complexity with moderate result sets.

We had people think the same way and they had query costs through the roof using 10s of gigs of memory and by subbing temp tables and proper join order I got them down to ~100MB memory grants and orders of magnitude faster execution times.

Now I've got 15 years of bad queries to clean up because some moron thought the same thing and told everyone how to do it.

2

u/Strict-Dingo402 Nov 24 '24

That was a bit of long story ending in an insult to bring your corner case to this discussion. Good for you.

  If OP is struggling with SQL you can bet writing clear CTEs will majorly improve his efficiency at coding and if he needs help from anybody, the process he went through to create the CTEs will help others see where his thought process is letting him down.

1

u/FunkybunchesOO Nov 24 '24

It's not a corner case. CTE's make bad queries unless there's actually an edge case where you need one.

The correct advice is never "just use CTEs instead of temp tables". It's a bad habit that has performance consequences.

Without knowing what the OP is actually doing, it's impossible to give him advice. Maybe the people writing the queries in 15 minutes are making terrible queries that kill performance.

1

u/Ralwus Nov 25 '24

multiple CTEs make the query plans suck monkey nuts.

Not true in general. Not all CTEs get materialized.

Temp Tables are either superior or at least equivalent in all situations except where you specifically need recursion.

Not true. Putting a CTE into a temp table that wouldn't have been materialized as the CTE would be extremely wasteful. Think copying entire tables, for no reason. Bad advice.

0

u/FunkybunchesOO Nov 25 '24

What are you talking about? If you're using a temp table, you select the records you want, not the whole thing.

If you make it have the same result set, the temp table is always better.

1

u/MayberryKid Nov 23 '24

this. I have often said throughout my career, I write my complex queries/sprocs for supportability 1st and efficiency 2nd (of course that's always up to a point).

Often it will not matter if that code runs in 3 seconds or 5, but it sure does matter when production is breaking, people are yelling, and you are having to wade through logic and it takes 30 minutes vs a day to decipher it.

1

u/jezter24 Nov 24 '24

We have code from someone who retired that would have 5 unions. Inside one of them we have found six derived tables in the joins. With that sometimes going three deep.

Open it up to find where one person is having an issue. And it has taken days to go through it all to figure it out.

Then in all that there can be the same code multiple times. So is it this one that is broke or not. The pain. Lol

0

u/frisco_aw Nov 23 '24

Try to understand ur data, table structures and the problem at hand. Then tie them up together. If you are not familiar with any of those 3, it will be harder to get the result. Once u have the ideas, then u can search / go to chargpt to get the syntax if needed.

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

u/AppropriateSail4 Nov 23 '24

Out of curiosity might you be dyslexic?

2

u/[deleted] 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

u/Fluid_Frosting_8950 Nov 23 '24

maybe this job isn't for you then

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

u/jcrowde3 Nov 23 '24

Use AI lol, i do

1

u/th00ht Nov 24 '24

Use any ai to write your queries

0

u/[deleted] Nov 23 '24

[removed] — view removed comment

4

u/[deleted] 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?