how I CRUSH Data Analyst Technical Interviews

[@LukeBarousse]

00:20 The bank account screen... "$no money bruh".... I died 😂🤣 More skits like this Shashank!!

[@ShashankData]

Learning from the best ;)

[@shikharsaxena8984]

One of the best websites for preparing for Data Science and Analytics interviews, being using it for quite sometime, highly recommend to everyone 👏🙂

[@danielhrdy2592]

Soon it's gonna be a year watching you, it's great to see both of us grow in the data field and you in the video production!

[@ShashankData]

Thank you so much Daniel! Let's keep crushing it

[@alvarochinchayan6035]

Really can't find content more valuable than this. Appreciate all your time and effort Shashank -- keep up the good work!

[@ShashankData]

Of course Alvaro! I have a ton of fun making these!

[@airamalhotra7474]

Cool intro Shashank! I liked knowing about your thinking process while framing a complex query. Also, I learnt more from your error corrections.. it helped me concentrate better. I would have zoned out midway had you got all three questions right straight away. All in all great video!

[@valiuddinqureshi6492]

I love watching your content. Looking forward for more skits in the upcoming videos. Gives that entertainment punch to the learning.

[@knwr]

I found what I believe to be a mistake in the second question, however it did accept your answer so maybe I am the one who is misinterpretting. When you create your CTE table, only nationality is included and not "host_id". This could return unintended results. For example, if the reviewer gives 10 different USA citizens one 10/10 review each, but also gives 1 single Australian 5 separate 10/10 reviews, the query will return "USA" as the favorite nationality. However, the question asks: "What is the nationality of the _favorite host_ " which in this scenario should be Australian. I believe one more Window Function is required to determine which guest receives the most 10/10, and then the nationality of that guest is the answer. This query then results in duplicate results since there are ties (Ex. from_user 1 gave 2 Brazilian hosts 4 10/10 reviews each), so the duplicates must be removed as requested (I used group by). If you have time, let me know what you think. Here's what I believe the correct query is for what the question asks: SELECT from_user, nationality, rev_count FROM ( SELECT COUNT(review_score) AS rev_count, from_user, nationality, host_id, MAX(COUNT(review_score)) OVER (PARTITION BY from_user) AS max_reviews FROM ( SELECT review_score, from_user, nationality, host_id, MAX(review_score) OVER (PARTITION BY from_user) as max_score FROM airbnb_reviews LEFT JOIN airbnb_hosts ON airbnb_reviews.to_user = airbnb_hosts.host_id WHERE from_type = 'guest' ) a WHERE review_score = max_score GROUP BY from_user, host_id, nationality ) b WHERE rev_count = max_reviews GROUP BY from_user, nationality Also thanks for the high quality material. I recently found your channel and really enjoy the content. 👍

[@bapu462]

The GROUP BY clause was not necessary for the 3rd question, because there are no aggregate functions being used in the query.

[@taz2392]

Guys I work full time 10-7pm with 3 kids and 41 years old. I’m the IT guy in the office with no degree and I know if I ever got fired I’d have to start again as I’ve only been paid more and more out of loyalty. I finally have some time to study and I would love to analyse data and help make reports by running scripts and presenting them. I thought I’d start with learning SQL from Colt Steele udemy course. Was wondering what your thoughts were as I have limited time to study and learn. Thank you.

[@tuandino6990]

Nice intro. Thanks for your work shashank

[@anggipermanaharianja6122]

for medium question [Favorite Host Nationality] we could also use rank() window function in our CTE, simpler and clearer like this with cte as (select ar.from_user as user_id, ar.to_user, ar.review_score, ah.nationality as nationality, rank() over(partition by ar.from_user order by ar.review_score desc) as rank_ from airbnb_reviews as ar left join airbnb_hosts as ah on ar.to_user = ah.host_id where from_type='guest') select distinct user_id, nationality from cte where rank_ = 1

[@ShashankData]

Great solution!

[@luigiruiz802]

Thanks for the examples!

[@sjord588]

Thank you so much for making this video! :)

[@taz2392]

One of the best KZbinrs around. Thank you

[@arielchocobar9608]

Shashank I keep watching your videos, this is what I want to do with my life, data science. you could be a celebrity in the data science world and we your biggest fans hahhha. but seriously thanks for all the efforts you make to teach us.

[@jotaemedlf5777]

That intro was fire🔥

[@ShashankData]

Thank u so much!

[@aidanthompson5053]

CTRL forward slash to comment out multiple lines of code

[@FunkyPretzel0911]

with temp as (select a.from_user,b.nationality,max(a.review_score) as top_score,dense_rank() over(partition by A.from_user order by a.review_score desc) as rank from airbnb_reviews a join airbnb_hosts b on a.to_user=b.host_id where a.from_type='guest' group by a.from_user,b.nationality,a.review_score order by from_user,top_score desc ) select t.from_user,t.nationality,t.top_score from temp t where t.rank=1; CORRECT ME IF I'M WRONG FOR 2ND QUESTION

[@yasuke8209]

>= 3 because it will still bring up beds that are more than 3

[@EdDy241907]

Haha love the intro!

[@alfredalonzo5456]

I learned a big deal, thanks man

[@saloriasaxon7323]

Yeah...that skit!🤣🤣🤣 You, Luke and Alex are great!!

[@Abdullah_Khaled]

Amazing video, but I'm wondering why you would use group by clause even if no aggr. function in the select statement (especially when using subquery) ?

[@humanalltoohuman]

"No money bruh" I felt that.

[@asian1599]

at 19:12 could you use DISTINCT on nationality instead of group by 1, 2?

[@ShashankData]

Yes that would be an option as well!

[@KNNPodcast]

That avo toast be hittin hard

[@ShashankData]

Basically why I'll be renting for the rest of my life 🤣

[@RiiicoSupreme]

You are the greatest man

[@keifer7813]

10:15 Isn't the from_user column the reviewer, not the guest? The from_type column tells you if that review was left by a guest or host. Correct me if I'm wrong

[@theJYC]

Loved the intro skit Shashank! Your editing skills are getting stronger by day (secondary to your data skills ofc) 🙌🏼

[@theJYC]

And yay for sponsored content 👏🏼

[@ShashankData]

Thank you so much Jin Young! 😁

[@Alex-yy6oi]

love your videos!! Do you have any videos on managing packages in python?

[@ShashankData]

Thank you so much Alex! Here is a link to that video: kzbin.info/www/bejne/gqjEq3uLoLpof7M

[@maisarahmonier2256]

13:38 why did he need to alias this????? can someone explain the error?

[@helloworld2696]

i also find 2nd more difficult than 3rd :)

[@Ninjawwmmmmmmm]

Hey Shashank don’t mind me but you spelled “avocado” wrong 😉. That intro was fire nevertheless!

[@ShashankData]

LOLOLOLOL great catch!

[@albertbarber1203]

Whats this landing page he has when opening google? How do I get it?

[@JustinInBlack]

funny outro; thankss

[@lekanfagbuyi7963]

This video intro is everything 😂

[@abdel1059]

hello your channel really motive me to start learning data analyst and i started but can you make a video about how can a fresher data analyst get a job remotely from anywhere in the world with a company if that happens or not a ask that because in my country all data analyst should have a degree from university and i don't the only way that i get job is freelancer or hired from a company remotely i think you understand my question and how to become really attractive to be hired thx you sorry for my english

[@aces8481]

bro when you're unemployed that avocado toast becomes a luxury 😂

[@paulp4475]

i gave a like just for the intro

[@ShashankData]

Thank you so much! I wasn't sure how well it would go over

[@keifer7813]

That skit was great lmao

[@michael_bryant]

$no money bruh

[@halowaffles]

"broke bois dont get points" Me: :')

[@abdallahelra3y118]

You watch anime :)🖤

[@Dota2seconds]

strike par jayegi Bhaai!

[@Dota2seconds]

music hataoooo