how I CRUSH Data Analyst Technical Interviews

➡️ Skip to 1:38 for the questions ⬅️
**the ad revenue from this video will go to making Eminem just a little richer**

Getting ready for interviews can be stressful, just remember: Success biases the prepared. So practice as many SQL and Python questions as you can. Finding SQL questions and Python questions for Data Analyst and Data Scientists used to hard, but this week's sponsor StrataScratch has thousands of questions for you to practice. Comment your favorite question below.

My FREE Courses:


Sponsored by StrataScratch

[LukeBarousse]

00:20 The bank account screen... "$no money bruh".... I died 😂🤣

More skits like this Shashank!!

[shikharsaxena]

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

[danielhrdy]

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!

[taz]

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.

[FunkyPretzel]

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

[alvarochinchayan]

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

[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. 👍

[airamalhotra]

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!

[aces]

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

[asian]

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

[aidanthompson]

CTRL forward slash to comment out multiple lines of code

[yasuke]

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

[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) ?

[arielchocobar]

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.

[bapu]

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

[valiuddinqureshi]

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

[keifer]

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

[abdel]

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

[helloworld]

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

[maisarahmonier]

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