How a Data Analysts Solves an SQL Problem (comment a better solution below)

Рет қаралды 11,109

2 жыл бұрын

Download files here: github.com/kshashank03/SQL-Ve...
Today we're working on forward filling rows in SQL using window functions, continuous table expressions, and pivots!
My FREE SQL Course: • SQL for Data Analysts ...
Patreon: / shashankkalanithi
MX Master 3 amzn.to/3sTroBW
LG 35in Curved Monitor: amzn.to/39pPzR3
USB-C Hub: amzn.to/31Ip8Sl
MacBook Pro Retina 16 Inch: amzn.to/2PSwZde

Пікірлер: 22

@jesse1586 2 жыл бұрын

Thank you for the video, it's very helpful! Is it possible you meant Common Table Expression (CTE) instead of continuous table expressions? This SQL problem can also be solved with a Cursor in T-SQL.

@ShashankData 2 жыл бұрын

hahaha you're 100% right! Great catch! I never used cursors all that much, but definitely want to check them out

@snidhin9066 2 жыл бұрын

just start SQL and this just made me feel I got a loooooooooooooonggg way to go!

@jordanarce17 2 жыл бұрын

I had a similar question in a technical interview a few weeks ago. Very helpful video!

@rsukut5866 Жыл бұрын

I love how you leave in some errors and how sometimes you have to Google things to find the right rules and syntax. Makes me feel a lot more comfortable about not having everything completely memorized, as long as I know how to interpret and correct the errors and find help for the proper syntax when needed.

@kgotsohopelekau 2 жыл бұрын

This Channel is about to take me from Zero to Hero

@juliantagell1891 2 жыл бұрын

These sorts of walkthroughs are really helpful. Not only to get to the solution but to takle them ourselves and to see how someone else went about it. Would love some more of these types of videos Shashank

@NathJones11 2 жыл бұрын

Massive hat tip - it’s refreshing to see you work through the errors that you come across, rather than editing them out. Any coding language is far from forgiving when pressing enter. It’s good to see that you use the same techniques to resolve problems simply googling them 🤣. It’s just it’s takes me significantly longer 🤣 Keep up the good work!

@ShashankData 2 жыл бұрын

Thanks so much Nath! We try and keep it real in the channel

@matheusm6786 2 жыл бұрын

Hi, here i'm again. I just wanna say thank you. Plz, bring us more sql content. I am in an internship now, SQL is basically 60% of what they do. The 40% is automation, pyspark and dashboards. SQL can be hard (is being to me) , because you need a lot of pratical logic that is just developed writing sql.

@mozzarella3416 2 жыл бұрын

I’m just on my phone so I can’t test this, but I feel like that was way more complicated code than necessary to do what you want. If the point was to find a less common technique and learn something new, then that’s perfect and you did. But if you were looking for most efficient and minimal code, I think you could do it be creating 2 sub queries or CTEs and joining them together by the ID. Get min date and the V1 info in a table grouped by ID. Get same for V2 in another table. Join to master table that has ID. It isn’t clear to me though from the way you ended it which date you actually want and what the desired final output was supposed to be though, so I apologize if what I’m proposing was considered and won’t work because of something I’m overlooking. Good video either way - you explain things very well!

@mozzarella3416 2 жыл бұрын

In MS SQL I think they require you to specify explicitly the column names when doing a group by, unlike in order by where you can do 1,2,3,n. Pretty sure that’s what was throwing your error

@omar.khan23 2 жыл бұрын

Hey…are u using excel through a virtual machine on ur macbook..if yess it would be helpful if u do a tutorial on this. Thanks

@ixternal9295 2 жыл бұрын

Bro your final data is bugged - some of the devices had no version2s yet you still took the value above, which was from a different device ID. For example, FSQP - it went from vv9 to vv8?

@fast4x 2 жыл бұрын

Could you possibly do this with Case When statements?

@smellypunks 2 жыл бұрын

This can be done in Pandas using query, pivot, reset_index, ffill and drop_duplicates. I was not able to method chain ffill so would be interested if anyone was able to accomplish this without making an intermediate variable.