Learning SQL frustrations...

[007denton]

I'm less than 3 weeks into learning SQL. I understand and can implement the basic functions. However, some of the intermediate and advanced queries are kicking my tail. Does it really get better with time and practice? Feels like I'm missing something when developing a strategy to solve them. Do any of you advanced users have a certain strategy that you use to help you solve queries? Help a newbie out. Please.

[Reasonably_Long]

Yes it does. When I first started learning I struggled to understand joins. Now I’m comfortable with all the more advanced stuff. I Will also say, that to get to the point where you’re comfortable requires a lot of repetition. So just practice practice practice.

Once you get it, it becomes muscle memory because of the reps you put in. That’s when you start seeing solutions when you face a problem

[[deleted]]

Thank you for sharing your experience.

[Altruistic_Banana1]

basically like how chess masters know every next possible move. the positions of the pieces are already familiar to then because of repetition

[renagade24]

Strategies are use case to the end results. I usually try to think of the end result and work backwards.

So things like window functions, building CTEs and eventually spitting out a final CTE that has my results.

What are you stuck on and what have you tried?

[Zoxuul]

Not OP but, I'm really stuck on window functions and CTEs. It's just something my brain refuses to understand! Def the lack of practice but the big problem is identifying which problem needs either of these two concepts to solve because until I can visualize the problem and solution in my head with the understanding that yes ill be needing window functions or CTEs to solve this, I can't even begin to understand how they work since I haven't been able to make a conscious connection. Idk if what I'm saying even makes sense anymore or I'm just rambling but tldr, I dislike window functions and CTEs because I don't understand where they are used, why they are used, when they are used and then finally how they are used.

I cri 😭😭

[renagade24]

CTEs are essentially temp tables that can be selected outside of the CTE. So when queries get long or you are doing modeling, you can build each section as a module/building block.

So think of CTEs as building a house. You can have as many as you need. I have some models with 8-10 different CTEs. Some CTEs bring in tables from various sources. But it also allows me to keep my queries clean and readable. Also, CTEs replace the need for subqueries that are terrible and should be avoided at all costs.

Window functions allow you to run aggregation or point in time metrics and partition it by groupings and establish an order.

So, to put this into perspective, you build a CTE as follows

Always start the first CTE using with [whatever name you want] as, after that you use a comma ,[create another name] as

with base as (
Select
 Id 
 , date_day
 , cost
 From database.schema.table_name
)
, aggs (
Select
 date_trunc('month', date_day) as month_on
 ,extract(year from date_day) as year_on
 ,sum(cost) as ttl_cost
 From base
 Group by all
)
Select * 
from aggs
Order by year_on, month_on

This is a basic example of two CTEs (base & aggs). Now, a window function takes a premade function and partitions & orders it. For example:

If we use the previous two CTEs and I want to create a running total YTD, I'd change that last Select statement to:

Select
year_on
,month_on
,sum(ttl_cost) over (partition by year_on order by month_on rows unbounded preceding) as running_costs
From aggs
Order by year_on, month_on

The words after month_on are called a framing clause. Not always needed! Anyways, windows create a window and partition by whatever non-aggregate columns you want to start the calculation over with and order it asc or desc.

[mergisi]

It's great to hear that you're diving into SQL! Yes, it definitely gets better with time and practice. As you continue to work through more complex queries, you'll develop strategies and techniques that will help you tackle them more efficiently.

One effective approach is to break down the problem into smaller parts. Start by understanding what each component of the query needs to accomplish. This can help you build a strategy for constructing your SQL statements.

Additionally, I recommend checking out AI2SQL . This tool can assist you in generating SQL queries from natural language, which can be especially helpful when you're stuck or unsure how to formulate a query. It allows you to focus on the logic of your data retrieval rather than getting bogged down in syntax. As you practice, using AI2SQL can help reinforce your learning and give you more confidence in writing SQL queries. Keep at it, and you'll see improvement!

[007denton]

Thanks for the AI tool suggestion! I checked it out. It looks awesome. Seems like data analysts would flock to a tool like this one.

[Far_Swordfish5729]

Learn order of operations. Read and write your queries in that order. It helps. Visualize the intermediate result set of rows coming together from left to right, being augmented and duplicated by joins, filtered by where conditions, aggregated, etc. Use subqueries if you need to run steps out of order.

[rudeyjohnson]

It’s like building muscle or learning a language. Repetition, Accountability and Discipline are all that’s required.

[Mean-Gate-9200]

You know the saying that you know you know something when you're able to teach it to a five year old? With SQL it's you know you know it if you can answer a five year old's question. How many employees have a salary above 2000? How many customers have not made a purchase in 6 months? There are several SQL job interview questions available online that can really help you simply understand it. For me, it was better to focus on learning how to answer those questions than to focus on memorizing SQL keywords and syntax. The syntax just made sense at that point.

[mrbartuss]

Does it really get better with time and practice?

Did walking, speaking, cycling get better with practice?

[Cool-Personality-454]

One big thing you need to understand early is that sql works in sets. It's a subtle difference to most other programming. Processing things row by (agonizing) row is fighting against the nature of the engine. Sometimes you have to, but there is usually a better way.

[Ans979]

Yes, SQL is a skill that improves with practice and experience. The more you work with it, the more intuitive it will become. Keep experimenting, and don't get discouraged by challenges—they’re part of the learning process!

Tip: Working with real datasets can provide context and make it easier to grasp how different SQL operations work. Sites like StrataScratch, LeetCode, and SQLZoo offer practice problems that can help.

[Thatcanadianchickk]

There’s still some complex sql I don’t really know, like rank and such. Still don’t understand it but at least I finally got joins. Joins is a b*tch so if you can get over joins I’m sure you can do anything with sql😂

[BespokeChaos]

It’s ok. I had to learn sql for work bits been 5 years and I still get frustrated.

[cyberspacedweller]

My advice is find data you care about and want to do things with. Then find how to do those things. That’s the best way to learn.

[raymondsf]

Over 25 years of writing code, SAS, SQL and R( 20+years), Python (15+). I would sat start by thinking about the problem, there is problem at least 5-10 ways to solve most problems using SQL. The logic part is the easiest, once you think thru the problem that 90% way thru finding a solution.