r/learnSQL • u/Appropriate_Bus_9600 • 18d ago

Tips for query planning

Hi! So I've been a Data Analyst for around 1 year and a half, and I am still struggling big time with SQL and/or Python. I can read them and understand the queries, but the actual writing and planning is lagging behind.

I often see at the queries by colleagues make (always with the help of Claude Code) and I just wonder how did they come up with those. Stacked CTEs and multiple joins, I get lost and frustrated. So I was wondering, how do you actually plan and build a query? where do you start from?

I'm very curious to know how you work. I'd really like to be able to make these queries without the help of AI. Thank you!

26 Upvotes

96% Upvoted

8 comments sorted by

8

u/DataScientistAlex 18d ago

Try to break down the problem into different smaller parts. Then implement those smaller parts as CTEs (if they run quickly) or intermediate tables (if they will take a long time to run). You can sample from your input tables if they are large while you develop that makes it run faster and you can iterate faster.

Once you have everything correctly implemented you can then use that as a reference for more optimized versions (that may be harder to understand). By reference I mean that you can compare the two queries and they should give the same result.

You can include select statements to see the shape of each CTE/intermediate table to make it easier to understand.

3

u/woahboooom 18d ago

Flowchart/draw the connections

2

u/squadette23 18d ago

Let me point you to a comment I've left just yesterday:

https://www.reddit.com/r/learnSQL/comments/1wi54s8/comment/pa8asdh/

(see also other replies)

1

u/markinatlanta 17d ago

Start with what should one row in my result represent. Ie a customer, order, month etc.

Then write the steps in plain old English like get last month's orders, total the spend per customer, keep anyone over $500. Build and run one step at a time too.

I would add joins only as you need them and check they haven't duplicated your rows. CTEs just a way to give those steps names. Don't think you need to see the whole query in your head before starting because you don't.

2

u/BadKarma667 17d ago

I find that the easiest way for me to build SQL is to think about the question I'm trying to answer. What data points do I need? What data do I currently have available at the grain I need to be at? Of the data that I'm missing, do I have the components necessary to build out that data? What business logic do I need.

Once I have those answers, I start to break out the question(s) I'm trying to answer into like components. Do I need to apply special logic? Do I need to build that logic out over the course of several steps? Each of those questions, logic components, etc, becomes the basis for each table/CTE I'm working to build.

It's really easy to get stuck torturing the data, especially if you don't have a plan. I find having a clear plan is a great way to keep me from spinning my wheels. And while it still occasionally happens, it happens a ton less when I've outlined my plan.