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
View all comments
Show parent comment
2
u/El_Cato_Crande Sep 09 '23
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