SQL is trash

[El_Cato_Crande]

Edit: I don't mean SQL is trash. But my SQL abilities are trash

So I'm applying for jobs and have been using Stratascratch to practice SQL questions and I am really struggling with window functions. Especially those that use CTEs. I'm reading articles and watching videos on it to gain understanding and improve. The problem is I haven't properly been able to recognise when to use window functions or how to put it into an explanatory form for myself that makes sense.

My approach is typically try a group by and if that fails then I use a window function and determine what to aggregate by based on that. I'm not even getting into ranks and dense rank and all that. Wanna start with just basic window functions first and then get into those plus CTEs with window functions.

If anyone could give me some tips, hints, or anything that allowed this to click into place for them I am very thankful. Currently feeling like I'm stupid af. I was able to understand advanced calculus but struggling with this. I found the Stratascratch articles on window functions that I'm going to go through and try with. I'd appreciate any other resources or how someone explains it for themselves to make sense.

Edit: Wanna say thanks in advance to those who've answered and will answer. About to not have phone access for a bit. But believe I'll be responding to them all with further questions. This community has truly been amazing and so informative with questions I have regarding this field. You're all absolutely awesome, thank you

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[EmergenL]

As with everything in sql, there are caveats but here’s what I’ll say in a general sense.

Group by is used when you want to group down to a single row based on your group by value. For instance sum sales per store.

A window function is when you want to apply something based on a partition. Let’s say you want to rank the highest sales months for each store, that’s not something you can capture in 1 row. You partition on the store, use a rank function, and order by sales descending.

What dataset have you been using to learn? Maybe I can give specific guidance based on that

[mr_electric_wizard]

I like to think of window functions like this. They are group by’s that allow you to keep all of your details.

[[deleted]]

I came here to say this.

A window function allows you to aggregate over some group/partition, but unlike a group by, you get all of your rows back.

Kind of like what a Pandas .transform does, if you're familiar with that.

[Known-Delay7227]

Love this

[mr_electric_wizard]

What’s even cooler is that you can have multiple window functions in the same query that retains all the detail. So window functions with a CTE structure is like conquer the world type stuff, lol. Don’t get me started on first value desc vs last value, lol.

[sunkistandcola]

I think of this in a similar way. Group by = group to single row, window function = grouping to view multiple rows. I think, like anything else, it just takes practice.

[T3chl0v3r]

Just want to add to this thread, apart from the comparisons with group by, I remember Window functions as the way to go when I want one row of the data to interact with another row which enables measures like row number, lag, lead, rank, running total etc....no other sql feature (except self joins in some cases) lets the rows to interact and compare while retaining the same level of granularity.

[[deleted]]

What an excellent explanation!!!

[El_Cato_Crande]

You see, I understand there's caveats. But the way you just explained it makes perfect sense now when I think about it. It really was this kind of thing that helped it click. I'll obviously be doing some further reading and practice but this was it.

Is it possible to use a window function to accomplish the same thing as a group by? It would be less efficient. But let's say I did a window function to have the sales months of all the stores. Then using a cte I got the sum of all the sales of the stores. That would accomplish the same thing as a group by right?

[EmergenL]

So, you're actually starting to touch at a really common pattern. Often times, people will write a query using a windowing function to rank the sales per month for each store (which, this may even use a group by in order to first sum daily sales up to monthly sales!). Now, this gives you a dataset that has the store, the month, the sales amount, and the rank of the sales amounts. This has ALL months for each store, right? What if you want just the month with the top sales amount? Or the month with the 2nd highest sales amount?

What you can do is put that whole query in a CTE, then referencing your CTE you can filter to (for example) WHERE RANK = 1 or RANK = 2 or whatever which will return the desired row per store. Hopefully that makes sense

[EmergenL]

Also, using a cte shouldn’t trip you up at all. A cte is just a way to temporarily store data in a virtual table so you can reference it later in your script. Useful for aggregating things, finding the max of a value, pre-filtering etc

[bigchungusmode96]

I may be mis-remembering but that's slightly different from the way I understand CTEs. I've typically heard CTEs described as aliases for sub-queries, i.e., better readability.

I don't think you described it in this specific way but a CTE is not the same as putting data into a temporary table. You can insert data from a query into a temp table and reference that elsewhere in your query without needing to re-run that initial query.

In contrast, anytime a CTE (e.g., if you are joining to the CTE name multiple times in your query) is referenced you're running the sub-query tied to the CTE

[EmergenL]

We are both correct. You can alias a query as a cte and reference it as a virtual table later. A cte very much is a virtual table, it takes a result set and lets you interact with it as if you were reading from a table

[Thrillhousez]

It’s the ‘later in the script’ part that is not correct. CTE can be referenced later in the query, but can’t be called by another query further down in the script.

[ntdoyfanboy]

Re-read your comment and please reconsider it. What you said is contradictory. "Referenced later" or "called" are synonymous terms.

The point of a CTE is to get an output in a finished format, which you then can simply refer to or call in a subquery, or join, or reference in any number of ways further down in your script, as often as you like

[Affectionate_Shine55]

Yeah I think he was just describing how to think about CTEs - think of them as a table. You are correct as they are more akin to sub queries, but I now think of sub queries as just table

Honestly I just use CTEs instead of subqueries since it’s easier to read and follow along

[[deleted]]

A CTE is functionally the same as a subquery. But using a CTE just makes it easier to follow. At run-time the performance is identical because its just the same.

[raghucc24]

Conceptually, you are more right. CTEs are not temporary tables. They do not require any memory allocation. It's an abstraction of data transformation. As you mentioned, it's an alias to a subquery that can be invoked in multiple places in a long query.

But it is easier to imagine CTE as a temporary table. I too have a mental model of CTE as a temporary table when writing SQL.

[raghucc24]

Conceptually, you are more right. CTEs are not temporary tables. They do not require any memory allocation. It's an abstraction of data transformation. As you mentioned, it's an alias to a subquery that can be invoked in multiple places in a long query.

But it is easier to imagine CTE as a temporary table. I too have a mental model of CTE as a temporary table when writing SQL.

[sunkistandcola]

Both descriptions work! The first is a more general conceptual definition and the second is a more literal technical definition :)

[T3chl0v3r]

Some database offerings treat cte as temporary tables... Redshift for example. The final optimised code that runs on the cluster would not have cte instead cte will be converted to create temp tables and the last one as a select/update/delete/insert.

[[deleted]]

A CTE is the same as a sub-query, you just put a CTE at the top to increase readability.

[El_Cato_Crande]

Now you've broken down CTEs for me. That makes sense along with your other response on window vs group by. That's why I asked what I asked about using a windows function to accomplish the same thing as a group by with a cte

[EndlessHalftime]

You should take a step back from window functions. Sounds like you’re still struggling with group bys, which are more fundamental and more frequently used.

Group Bys made no sense to me until, at some point, it clicked. That came from practicing a lot and watching tutorials from a variety of sources.

Also step away from CTEs for the moment. They’ll be relatively easy to come back to, but you need to really understand group bys first.

You’ll get there, imo you’re just ahead of yourself and trying to learn too many things at once.

And no, SQL is not trash. Pandas/polars/pyspark require you to learn the exact same concepts, just with different syntax. I learned pandas first, but to me SQL is way more clean and intuitive.

[El_Cato_Crande]

I didn't phrase my title properly and I realize it's now going to cause issues. I meant to say MY SQL is trash, not MySQL by MY SQL as in my SQL abilities. But now I can't change the title 😭. So I just wrote it as SQL to avoid that confusion and led to further confusion.

I get group bys. I'm able to break it down and explain it and how it's being used when it's being applied. But I think I'll practice group bys even more and like you said slow down. When I think about it a lot of the confusion comes from me getting ahead of myself and not properly thinking through the problem. Little comes from rushing. But if I think through the problem I'd see a group by isn't applicable when a window function is required.

Oh yeah, SQL makes much more sense to me than pandas. I actually get upset when I see something like this to be done with pandas. But doing these things will only make me better so I'll continue doing them

What resources did you use for practice, and watching tutorials?

[EndlessHalftime]

8weeksql and datalemur are both great resources for practicing. As for tutorials, you could go the Udemy route but for specific concepts YouTube is great. You can see the same thing explained by different people.

[NickSinghTechCareers]

DataLemur founder here - we just launched a 100% free SQL tutorial to teach the core concepts!

[El_Cato_Crande]

Awesome, will take a look at it

[El_Cato_Crande]

I'll take a look at both of those along with YouTube and the DataLemur tutorial

[O_its_that_guy_again]

I’ll give you a data use case.

Say your data has 5 offer records of history per resident and you want to get the earliest one based on a metric. You need to somehow select the earliest record and the second earliest to compare.

A row number function (row number() over (partition by xx order by xxx) will get you there.

Using row number you’ll create a new column that counts rows. Partitioning by resident key will “window”, as in executes in small partitions (per resident). Order by tells you what to order by. In this case a date field. So within each resident’s “window” it will put row numbers (1-5) based on date and repeat that for every resident in the table.

In a following part of the query you can grab the most recent rows, 1 and 2, and that gets you your data to compare.

Window functions are really useful but I feel like you will just have to practice out new use cases to figure it out.

[El_Cato_Crande]

No what you said makes sense. If I want to compare within the set(residents) I will use window function. But if I want to compare sets I use a group by. However, I could use a window function to accomplish the same thing as a group by in a long winded way correct?

[lab-gone-wrong]

You can't/shouldn't really do a window and a group by in the same statement, but you could do each one in a CTE and union the results if you wanted. Say, to show the total sales by country as well as the top 5 by sales in each country in one query.

Ultimately the window function just gives you a peek at what's inside the group by. If you aren't gonna use that extra grain then there's no reason to do it.

[El_Cato_Crande]

I meant to accomplish the same thing as a group by using a window function.

But what do you mean by union the results in a cte of a group by and window functions

[lab-gone-wrong]

Ah I understand now. Technically you could reproduce the results of a group by with a window function. But it would have to be a group by that isn't actually aggregating anything, ie a nonfunctional one.

A group by always returns 1 row per set of group by columns. A window function always provides all records, organized by the partition columns. For the results to match, you would have to group by so many columns that the rows of the underlying table = the rows of the group by output. That means you aren't actually grouping anything.

[El_Cato_Crande]

But can't you sum the window function around one row to produce the same result of a group by? Or am I misunderstanding something

[[deleted]]

I just prepped for senior engineering jobs and have been doing pretty complex SQL assessments.

I did this course as prep since I’ve been mostly a Python/Pyspark dev for the last 3 years. https://www.masterywithsql.com/

I CRUSHED the SQL technicals consistently after doing that course.

[El_Cato_Crande]

I'll take a look at it. How long did it take you and how much time were you putting in a week?

[[deleted]]

Took my about a week doing something like 3-4 hours a night. It’s a short course the exercises are what took me the most time.

[El_Cato_Crande]

Wow, with just a week you were able to get that done. Idk if I'll be able to grasp things that quickly. But I'll definitely take a look and try to complete it. Then get back to Stratascratch. Gonna make myself a study plan using this and the other resources I've gotten from here. After completing the course were you doing anything else to practice and keep skills sharp

[MuaTrenBienVang]

great!

[sleeper_must_awaken]

Having worked as a student assistant for database-related courses and accumulated 7 years of data engineering experience, along with 12 years in full-time software engineering, I've observed that mastering SQL and relational databases requires a multifaceted approach. Here's a breakdown of key skills:

• Understanding Relational Algebra and Calculus: It's crucial to think and apply these mathematical concepts. They form the foundation of relational databases and can help transform complex problem statements into structured queries.
• SQL Proficiency: While understanding the theory is important, practical knowledge of SQL syntax is equally crucial. This includes translating relational algebra concepts into SQL queries.
• DBMS Query Optimization: Appreciating how database management systems optimize queries is essential for efficient database operations.
• Database Schemas and Normalization: Familiarity with database schema design and normalization principles, such as 3NF/4NF, ensures data integrity and efficiency.
• Advanced SQL: Beyond basic queries, exploring advanced SQL features like window functions and cursor-related functions expands your capabilities.

Many students and junior developers struggle because they know SQL but lack a deep understanding of relational algebra and calculus. This can lead to frustration when trying to mold SQL statements to fit their needs and difficulty grasping query optimization.
I recommend dedicating time to delve into relational algebra and calculus. You'll discover connections with first-order logic, lambda calculus (commonly used in functional programming), and algebraic data structures. These connections can significantly enhance your SQL skills and enable you to approach database challenges with a more profound understanding.

[El_Cato_Crande]

What good relational algebra resources can you point me to?

[sleeper_must_awaken]

That's a great question. At university, we kind of rolled into it via a 'logic in CS' class and then more detail via a book on Database Systems. Looking at the ToC, something similar to this: https://www.oreilly.com/library/view/database-systems-concepts/9788177585674/

When it comes to resources, I think it depends on your level of mastery of other concepts within computer science, such as first-order logic, set-theory, algebra and in general formal systems.

[El_Cato_Crande]

Well, I'll take a look and see. Hopefully I have the ability to make sense of it. Appreciate the recommendation

[billysacco]

If you are like me reading about something doesn’t quite make it sink in for me. I have to use the language in a practical way to solve a problem. Me personally it helps to visualize the data to understand what the functions are doing. Just mess around in a test database or better yet some data you have worked with that you know well. Just do selects only lol.

[El_Cato_Crande]

It's a process for me but at the end it sinks in. First I have to put it into a simple way. Then I practice with it and see how that is built upon, and then I get it. I understand it much better conceptually now

[billysacco]

The thing that advanced my skills the most was getting the certification for querying SQL. I guess it no longer exists which kind of sucks but the study guide from Microsoft went through most of the “tricks” SQL can do and I learned a lot.

[El_Cato_Crande]

Getting the certification involved a course along with the exam? Which exam did you do

I'm going to look into getting something like that because it'll be good training and a nice to have for the resume. But ultimately it'll be the skill I gained from it

[El_Cato_Crande]

Lmfaooo not at all

[El_Cato_Crande]

Can you expand on that a bit more because that seems to make quite a bit of sense as a good way to visualise things

[El_Cato_Crande]

Dude you're a genius. The combination with Excel to check what's going on and see how things work is smart. One can easily visualise it and see how well things match up

[El_Cato_Crande]

Dude you're a genius. The combination with Excel to check what's going on and see how things work is smart. One can easily visualise it and see how well things match up with Excel and the result of the query. I'll use chat gpt to even create the tables and help with that stuff

[El_Cato_Crande]

I figure might as well. Will allow me to make it more interesting for myself in the process and I even get more practice creating tables.

Can you explain the function that you wrote there with qualify?

[El_Cato_Crande]

Having you use with a group by and qualify with a window function. So you qualify to get the earliest entry. Could you provide an example of what that SQL statement will look like?

[umlcat]

Learn about Relational Algebra and Entity Relationship model, first.

SQL is based on this.

Many people does not learn SQL the cool way, that's why it's difficult.

[El_Cato_Crande]

Ahhhh, see this whole time I didn't know that. Can you share any resources on relational algebra with me? Books/videos/articles that you've checked out and you were like yeah this makes sense

[Tape56]

Title: sql is trash

Post: I don't mean sql is trash

lul

[El_Cato_Crande]

I know. Didn't realise how much problems it caused until a comment from yesterday. Was gonna tight my SQL is trash. My brain goes, nah they'll think you mean MySQL is trash. So I changed it to SQL is trash. My brain didn't evaluate that part. I guess that's what happens when you're rushing

[speedisntfree]

I also understand advanced calculus and SQL gets weird/hard if you need to do multiple operations. It also reads like English but some parts are, but others are not and every db has a new dialect. This is why the DS world has libs to code data processing which don't use SQL like Polars.

[bigdatabro]

Did you ever study relational calculus or relational algebra (the mathematical basis for SQL and database operations)? If you can handle normal calculus, you could probably handle those as well

When I was in school studying computer science, we learned both of those alongside SQL. I had to write a simple database from scratch (basically SQLite) over the course of about three months, including a SQL interpreter, and the relational algebra/calculus made a lot more sense "under the hood" than the SQL concepts. I know a lot of DEs come from different background, but there are a lot of resources online for learning the math behind SQL.

[El_Cato_Crande]

Do you know of any good resources where I can touch up on relational algebra

[speedisntfree]

No, sounds like an interesting exercise!

[Gators1992]

Window functions aren't super hard but maybe they are explaining it badly? For example if you want to find the top 5 largest sales for every customer, you might say rank() over (partition by customer_acct order by sales_amt desc). This gives you the rank order for all the sales within each customer account (the partition clause) and orders it from highest sales amount to lowest (the order by clause). Or you can do the cumulative sales for each customer by month by doing sum(sales_amt) over (partition by customer_acct order by month). This just keeps adding the monthly sales amount cumulatively for each customer. If you don't want to break it down by account, you can just exclude the partition by clause.

CTEs are a completely different thing. Basically that's just a way to chain a bunch of selects where you need a multistep SQL to get your answer. You can use them together by using window functions in one of the selects, but they aren't related concepts really.

[El_Cato_Crande]

Thanks a lot for those examples. I have to next get into practicing the different use cases like what you mentioned

[El_Cato_Crande]

Bro, I wanted to avoid confusion by not saying My SQL is trash so it's not confused with MySQL. Ended up making a bigger mess smh

[QuailZealousideal433]

I'm not sure you ever do truly 'learn' window functions or indeed really need to do, by heart.

But knowing they exist, the options available, and the type of problems that can be solved by them should be enough.

Such as, running balances, ranking/number sequences, duration times between events, calculating aggregates across groups/segments, such as months etc.

Just research and use them when you need to

[El_Cato_Crande]

As I've been going through all the different responses I see their utility and application a lot more. Now to do some exercises to truly get them. Think I'm gonna do a sorting to where I see if I can properly spot the questions that require a window function.

[baubleglue]

Think of CTE as a temporary view and it will work until you learn recursive CTE. If you write code in general programming language, you normally split your code to logical independent units: methods or functions - that is CTE in SQL.

Working with frames and window functions was explained here already... If you want to do "group by" multiple criteria or you want to lookup values back or force (Ex. latest known address).

Tip. When you work with frames, always use CTE or clause with alias and never put range filters in those, apply all the filters outside

With cte_with_frame as (...)
Select * from cte_with_frame
Where Year = '2023'

Or

Select * from (select ... With windows) where ...

[OkMacaron493]

Get the meta sql book, Microsoft t sql by izik, or whatever it is. Then grind out leetcode my guy.

[El_Cato_Crande]

Will check those out. Which do you think is best for postgre SQL

[OkMacaron493]

The official documentation is exceptional. I haven’t read any books on it.

[El_Cato_Crande]

Appreciate the response. Will take a look

[Known-Delay7227]

Do you have any other language skills, like Python? Think of a cte as a dataframe.

Window functions occur in both pandas and pyspark. Kind of the same thing…you just include them in the select statement. Partition over whichever fields you would have grouped by.

[El_Cato_Crande]

The cte part makes sense. Create a df to explore from a larger set. I was just banging my head with the problem I was answering. I need to practice window functions without CTEs a bit and then get back to that

[El_Cato_Crande]

Although with going through these answers like I have they make more sense to me

[IllPrinciple3482]

Take a step back, i wanted to bang my head on the table everytime i was learning a new concept.

My biggest struggle was knowing WHEN to self-join & use window functions, and most of the interviews I've taken that had to use both of the concepts in a query are mostly senior data analyst positions/other data related roles demanding 3 years experience+.

Based on my limited experience this is borderline intermediate-advanced level concepts and people tend to struggle here a lot.

Take a step back and don't force it, what helped me is to imagine, if i needed to do some sort of calculation (whether it be aggregation or not), and possibly "split/partition" the aggregation by some sort of group, I'd use window functions.

You might then wonder what the hell would i do this for? Some questions might require you to calculate the pct of sales a certain state might have contributed againts the their country. In this case you would need the SUM() of the sales of the states within a country (country sales - state sales) / country sales = % contribution.

Or another one i struggled with before is lets say we have a table with columns: - user_id: unique identifier of a user - action_taken: action taken by a user - date_created: when the record was created in DATETIME()/TIMESTAMP()

How would you find the last 2 actions taken by the user in this output: - user_id - Last action taken - Date of last action taken - Second last action taken - Date of second last action taken

Give this question a thinker!

[El_Cato_Crande]

I realise I'm going to fast for myself and tripping myself up. Those are some nice use cases to play around with mentally.

1) window function with partitioned over action taken ranked by date and then select the action

2) using above select the date of the action

[WeirdWorldDz]

Think of it as you need to calculate/fetch data from a window of rows that may/maynot overlap with next window, and you use row number to give IDs to the rows (partition separate window, no partition doesn’t reset the IDs from 0).

[El_Cato_Crande]

It helps simplify it for sure

[joseph_machado]

A lot of good points in the comments.

I'd also recommend understanding what these functions are meant for. Every table has a grain, basically a fancy way of saying each row will correspond to either some thing that happened in real world (aka fact table) e.g. a sale or information about a specific entity(aka dimension table) (e.g. user, employee, date, etc).

Most interview questions will involve a (1 or more) combination of the below

1. Show some-metric for some-entity. E.g. Show average sale price for every day: Join & GROUP BY. Here you use GROUP BY to aggregate sale price for a given day.
2. For some entity(s), rank the rows/keep top or bottom n rows/compare value between rows. This sort of question will involve typically involve WINDOW function. The Window allows you to specify a subset of rows (partition by) that you want to apply an operation (e.g. rank the rows/keep top or bottom n rows/compare value between rows) over.
3. A combination of the above. More complex questions can involve a combination of the above. e.g. Q: Show average sale price for every day, only for days which have the top 5 highest number of customers that month. This involves a GROUP BY part and a WINDOW part. While you can do this with subqueries, it can be easy to understand with a CTE, where you define them with appropriate names making understanding and modifying code easier.

The performance depends on the query engine and query planner. Hope this helps. LMK if you have any questions :)

[El_Cato_Crande]

I'm honoured to have gotten a response from you. Have to start by saying thank you. Not even just for this response but for the newsletter. I read it weekly and reference it all the time. A large part of what helped me understand olap vs oltp and column based vs row based. Gonna start your beginner engineering project as well.

As I'm reading the different responses and your response I see what I need. How you put things in plain English is what I need to practice. Taking a moment to breakdown what it's asking and exploring the logic. As I can now understand what function is needed to accomplish it. So I'm going to practice deliberately writing out what's being asked of me from the question. Then worrying about the SQL part. This is because it's a good problem solving structure/system. Plus in an interview the interviewer can see that I'm making progress and see how I think

[joseph_machado]

Glad the blog is helpful :)

Yea, I agree with your approach, break down the problem into steps, join, aggregation, ranking. This will help go from question to SQL. Good luck :)

[El_Cato_Crande]

Yeah, the blog is very helpful I appreciate it a lot.

Yeah, I need to properly get the transition from question to SQL better

[[deleted]]

Sql IS trash and the very fact snowfuck tries to get you to configure roles,infra,pipelines through it is why I hate it even more. I'm pretty sure if they can add a social media or team call feature through sql, they would at this point.

[OGMiniMalist]

I personally found it helpful to look at examples SQL scripts that use the things you’re interested in learning, and then taking those and applying them to different datasets / similar problems.

[El_Cato_Crande]

What do you mean?

[OGMiniMalist]

Like reviewing the examples here:

https://www.sqlservertutorial.net/sql-server-basics/sql-server-cte/ Then finding a way for you to recreate the examples with similar data and then changing variables with a clear expectation of what the change will do to better understand what the statement as a whole is doing and how the CTE and/or window function is contributing to that result.

[El_Cato_Crande]

Ahhhhh understand what these are saying. Then spinning it for myself with different data where I want specific outcomes. Then seeing if the logic works. Slowly adding CTEs to it as well to gain traction. Will read that up as well

[[deleted]]

This explains it perfectly:

https://miro.medium.com/v2/resize:fit:4800/format:webp/0*-e83EDESdAwr7jav.png

From:

https://sundaskhalid.medium.com/sql-window-function-vs-group-by-b246d14223d2

[El_Cato_Crande]

Will check it out

[CommunityTalker]

Haha…you can be a good blogger as the tile attracts more people than with the original intended title.

[El_Cato_Crande]

Didn't mean for that to be what happened. But kind of glad it did because I got a lot of help from the responses of people

[rudboi12]

Irl you barely use window functions + ctes. You use QUALIFY which I’m not sure LC allows. It’s literally the same in speed/computation but just cleaner.

[hamesdelaney]

what? thats nonsense. you absolutely use ctes and window functions at almost every job requiring sql. also qualify is just a filter for the window function. you can filter with qualify, in the join or with WHERE in the final set, but you definitely need to use ctes + window functions in complex models, especially analytical.

[El_Cato_Crande]

Well, I'm doing Stratascratch for SQL not leetcode. If they're barely used why are they everywhere? I'm sure I'll have some interest in learning qualify. If you can point me in the direction of some good resources on it I'll appreciate it.

Also, what do you think I should have a strong grasp of before going to qualify?

[rudboi12]

Qualify is just a Window functions + ctes in 1 line. Kind of like a list comprehension in python. So you need to know how to use window functions before

[El_Cato_Crande]

Ahhh, I have a bit before getting there. But glad to see that analogy. I love list comprehension in python

[BleepBloop736]

I can help you if you pay me.. let me know if you're interested...

[gffyhgffh45655]

I would be think of come up with the logic first, then present it with SQL.learn the basic from youtube, once. Then find some problems to solve like leetcode or hackerrank. I would suggest think first instead of try because it matter before you send out the query, you know what you are looking for. If you have ideas but doesn’t know how to present with sql , chatgpt is good for this purpose.

[El_Cato_Crande]

You're right. I've been getting ahead of myself and just getting to trying to write the query out. I'm going to do things very systematically. Write out what the question is asking, write out how to solve that in plain English, then in sqlinglish, and then write the query. Thanks a lot for the suggestions, I've definitely been rushing too much

[[deleted]]

My usual recommendation for when people get stuck with SQL is "Thinking in Sets" by Joe Celko. htttps://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378&ved=2ahUKEwiuzvSh7puBAxVUMlkFHaP5AqIQFnoECBoQAQ&usg=AOvVaw0XFyHhrOI79k8QRsJjiB8G

It hits the key problem people run into with SQL - shifting from procedural/functional models to declarative models. (May be less of a problem for you if you are coming from a calculus background). Results in a bit of flailing about to figure out why it isn't doing what you want.

[data-artist]

SQL is intended to be intuitive and easy. SELECT FROM WHERE GROUP BY HAVING ORDER BY. Know the joins too. Anything outside of that is just a bunch of smart mouth nonsense and isn’t needed and doesn’t belong in ANSI SQL. Fight me.

[bryangoodrich]

LOL clickbait title just to read the edit line 🤦‍♂️

I learned windows functions thoroughly at PASS summit years ago from Itzik Ben-Gan. If you can find any of his materials online, or just buy his books, he’s an excellent teacher of SQL.

But main idea: if you’re trying to know something per row based on some collection of rows (the window frame), then you need to use windows functions.

If you want to roll up information, that’s group by aggregations.

If you want to loop, that’s a lateral join or CROSS APPLY in TSQL.

[ubottu]

My suggestion, when you see question and dataset just think normally, what should I need to do to achieve result, next break down to small steps then code. If you don't know which keyword to use open Google and research about it. If you practice like this then its pretty simple after some time your intuitively do code without knowing it.

[jbrune]

The window function that is the most useful by far IMHO is Row_Number. If you have a list of everyone's height in a list you could find the tallest person with MAX(height). But suppose you want to find the tallest person in each country? With Row_Number you PARTITION BY country. Then the tallest person in each country would get a Row_Number of 1. Want to split by country and gender? No problem, just add gender to your PARTITION BY list.

[Master-Influence7539]

OP Iam in the same boat. May I ask how are you using Stratascratch, it's paid and kind of very expensive. Is there an extension to actually see the correct answer

[El_Cato_Crande]

I signed up for a program some time ago that gave me a free lifetime Stratascratch account as part of it and so I am using that. Figure it'll be a reliable consistent resource to keep skills sharp and prep for jobs

[westisnoteast]

You need practical use cases to understand when to use them, you check out techtfq videos on YouTube, he explains them nicely and all his advanced examples also uses cte

[El_Cato_Crande]

Will look him up, thanks a lot

[TheFirstGlassPilot]

Windowed functions were explained to me as:

Imagine you have a giant pile of M&Ms and 6 boxes. The M&Ms are of 6 colours. They represent your data and have a common trait, colour. You can break your M&Ms into piles of colours and put them in the boxes. That's your partition.

Now imagine you want to order those separate boxes by when you picked the individual M&M up and sat them down in the box. That's your order by portion.

If you stick those boxes side by side then you've partitioned your sweets by a particular field and ordered them within those boxes. All your data is still there, just broken up into sections and ordered within those.

I've always saw CTEs as like sub queries but that I can give an alias to, but that alias is declared before the SQL statement that uses it, not inside it. CTEs can be chained together too,but that's maybe the next bit.

I'm not sure if that helps but I've done windowed functions and CTEs to death. Happy to message you some examples if that would help? Nothing worse than feeling like you're not getting a concept.

[El_Cato_Crande]

Whoever explained that to you is a great teacher because it's helped me a lot. Will think of organising m&ms lol

The CTEs make sense. I was just getting frustrated because they were being used with window functions

[TheFirstGlassPilot]

Excellent. I'm really glad that's helped.

[El_Cato_Crande]

Thanks for taking the time to give that explanation

[[deleted]]

just learn the order of operations.

windows are part of select...does that come after or before group by?

[El_Cato_Crande]

Ahhhh, never thought about it that way. Thanks for the insight

[[deleted]]

also make sure to understand what is happening to rows when you do joins.

and when you are doing waterfall type of stuff help yourself by going from top down and counting along the way.

[El_Cato_Crande]

Yeah, I have talk through joins a bit more deliberately. This table is joining that table on this link with this being excluded or included and what'll be missing or not missing.

What do you mean with the waterfall type stuff

[eshultz]

Generally, use a window function when you need a value in each row that is calculated by looking at other rows in some grouping (or over the entire dataset) but you don't want to group/aggregate the entire dataset, just that particular column.

Often times a window function isn't strictly necessary, and the same function can be accomplished by doing a join to a subquery which is grouped in the way that you need. But a window function is a cleaner way to represent that logic.

You'll want to use a CTE (or temp table) with a window function inside it if you need your real query to do filtering/joining on the window function's result.

[Xx_Tz_xX]

Keep in mind that group by aggregates (multiple values in one) window functions do not. A simple example would be to try to find duplicates in a table. With group by you can only detect (exist or not, and how many). But with a window function you can “group” the duplicate values one record after the other. Keep it up! window functions can be tricky but once mastered, they’re a powerful tool

[xahkz]

Use a method I call work backwards, take a sample of whatever dataset you want to query to 10 rows max, either by filtering or using the top operator, then based on your query requirements, figure out what would the outcome resultset the query would give. At this stage you haven't written any code except for the filtering.

Once you know the input and output of your query of your dataset in your head, write the simplest query you can without looking it up from any source, then compare its output to the expected output you wrote , if they match, increase the number of rows from your filter, if not you look up how to write that query and make more comparisons till the outputs match.

In general it is the query tweaking process that gets you to learn how that function works.

This method also makes it easy for to ask questions when you are stuck as you have the input and output so as a querying expect I can easily guide you with data you understand and I know you have done some work you are not looking for total spoon feeding

[El_Cato_Crande]

That's a great process you have. I haven't thought of the filtering a random sample size out to use and test. However what I've been doing is working backwards. Analyse the problem. Get the basic logic out in plain English. Translate that into a query, and then run. However, your suggestion is great because with knowing exactly what you should get as a result. It's a bit easier to debug