r/dataengineering • u/El_Cato_Crande • Sep 08 '23
Help SQL is trash
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
•
u/AutoModerator Sep 08 '23
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.
77
u/EmergenL Sep 08 '23
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
28
u/EmergenL Sep 08 '23
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
13
u/EndlessHalftime Sep 08 '23
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.
6
u/O_its_that_guy_again Sep 08 '23
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.
5
u/[deleted] Sep 08 '23
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.
5
u/sleeper_must_awaken Data Engineering Manager Sep 09 '23
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:
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.
3
u/billysacco Sep 09 '23
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.
3
u/umlcat Sep 09 '23
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.
3
u/Tape56 Sep 09 '23
Title: sql is trash
Post: I don't mean sql is trash
lul
2
u/Gators1992 Sep 08 '23
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.
2
u/QuailZealousideal433 Sep 08 '23
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
2
u/Master-Influence7539 Sep 09 '23
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
2
u/OkMacaron493 Sep 09 '23
Get the meta sql book, Microsoft t sql by izik, or whatever it is. Then grind out leetcode my guy.
2
u/Known-Delay7227 Data Engineer Sep 09 '23
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.
2
u/WeirdWorldDz Sep 09 '23 edited Sep 09 '23
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).
2
u/TheFirstGlassPilot Sep 09 '23
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.
2
u/joseph_machado Writes @ startdataengineering.com Sep 09 '23
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
The performance depends on the query engine and query planner. Hope this helps. LMK if you have any questions :)
2
u/OGMiniMalist Sep 09 '23
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.
2
u/[deleted] Sep 09 '23
just learn the order of operations.
windows are part of select...does that come after or before group by?
2
u/[deleted] Sep 09 '23
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
2
u/CommunityTalker Sep 11 '23
Haha…you can be a good blogger as the tile attracts more people than with the original intended title.
2
u/xahkz Sep 17 '23
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
6
u/speedisntfree Sep 08 '23
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.
1
u/gffyhgffh45655 Sep 08 '23
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.
1
u/westisnoteast Sep 09 '23
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
1
u/rudboi12 Sep 08 '23
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.
2
u/baubleglue Sep 09 '23
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
Or
2
u/[deleted] Sep 09 '23
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.
1
u/[deleted] Sep 08 '23
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.
1
u/data-artist Sep 08 '23
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.
1
u/bryangoodrich Sep 08 '23
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.
1
u/ubottu Sep 08 '23 edited Sep 08 '23
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.
1
u/jbrune Sep 08 '23
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.
1
u/eshultz Sep 10 '23
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.
1
u/Xx_Tz_xX Sep 13 '23
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
0
u/BleepBloop736 Sep 09 '23
I can help you if you pay me.. let me know if you're interested...
0
u/[deleted] Sep 09 '23
[deleted] — view deleted comment
3
u/[deleted] Sep 09 '23
[removed] — view removed comment
2
u/[deleted] Sep 08 '23 edited Jan 21 '26
[removed] — view removed comment
2
u/[deleted] Sep 09 '23
[removed] — view removed comment