Facebook's Most Common Data Science SQL Interview Question [2021 Interview Question and Answer]

This is the most common data science interview question from Facebook. It’s the most commonly tested concept on Facebook's coding interviews, especially in the beginning rounds.

______________________________________________________________________

______________________________________________________________________

Timeline:

Intro: (0:00​​)
Interview Question: (0:50​​)
Solution Approach: (1:50​​)
Explore data: (2:28​​)
Identify the required columns: (3:40​​)
Visualize output: (4:14​​)
Code in increments: (5:14​​)
Conclusion: (10:16​​)
______________________________________________________________________

About The Platform:

______________________________________________________________________

Contact:

If you have any questions, comments, or feedback, please leave them here!
______________________________________________________________________

#SQL​ #DataScienceInterview

[friendsplain]

Nate your content is gold! Thanks for your detailed walkthrough of the problem and solution. It is a huge help for data science interviewees like me!

[dwaipayansaha]

Hi Nate, my solution is:-
select (count(*))/(select count(*) from fb_active_users where country='USA') as monthly_share from fb_active_users
where status='open' and country='USA';

[mrblahblihblih]

Hi Nate, awesome video! Your content is really helping me with my upcoming interview with Facebook. I was wondering, would it have been possible to just do a left join with two subqueries instead? Or are you just optimizing for runtime

[MUSKAN]

This was really helpful! Thank you very much. Please do more such videos!

[ranitdey]

Your content is amazing. I love the way you have explained the solutions.

[PATRICKCHUAD]

Hi Nate, now I'm beginning to practice the steps on solving the problem, not just go directly and dive to the coding. your steps is very logical and can be very advantageous in more complex question. Divide and conquer is the focus to come up with a logical solution. Thanks for continuous sharing your knowledge.

[madhulikasuman]

hey guys, does the fb data analyst interviews has whiteboard or IDE for sql test?

[MrJohnbrown]

Hey @ Nate, great video’s and thanks so much for the practical scenarios. I have been asked about Mapping in a data analysis interview and was told alot of mapping will be part ofthe job. Any videos on that any time soon ? Thanks

[hhliu]

Hi Nate, I didn't get it why you didn't select the distinct user during your code. as count(case when status='open' then user_id else null)
if a user showed twice, it will be 2 for his count

[ranajparida]

Hey Nat, by looking the question, i am not able to find where question is asking about the ratio..???

[chrisshaw]

Is casting the ratio as a float something that needs to be done only in certain SQL environments? The mysql editor on leetcode does produce a ratio when dividing an integer by another integer. Maybe this is because I used the round function though to go out 2 decimal places?

[charansai]

Hi nate your vedios boost my confidence man Thanks a Lot

[zmeyk]

I wrote my code just before you explained your solution and the only thing I changed is I passed the step with doubling user_id checking, instead I counted them with distinct prefix "count(distinct user_id)", everything else was the same (including nested select).

[cookiemonstauhh]

Hi Nate i just wanna say thanks for the videos! Your data science interview question with solutions are really helpful and engaging:)

[radhsri]

Hi Nate, thanks for the awesome videos. At 7:13, I dont understand how count(NULL) will NOT count that particular row. I was going to use SUM(CASE(WHEN status = open, 0, 1)) but will a COUNT skip a row having a NULL value? thanks

[aishwaryareddy]

how to reply if the interviewer ask, " if you are solving the question in intervals, will you be doing the same while on the job?"
Just want to think of all possible questions can be asked in the coding interview.

[adwaithks]

I did it this way :

with usa_people_count as (
select count(*) as total_people_count from fb_active_users where country = 'USA'
), active_people_count_in_usa as (
select count(*) as active_people_count from fb_active_users where country = 'USA' and status = 'open'
)

select cast(active_people_count as decimal) / total_people_count as share from usa_people_count, active_people_count_in_usa;

[nilsaha]

User_id 82: country Australia, User_id 34: Donald Ross country China in the table. But when you filtered out the country, both of those users are shown in the output. Could you please explain it?

[mensenvau]

How can I apply for a Data Base on facebook?.

[priyankalad]

I am afraid if in real interviews we can test parts of logic or carry out code increment? What do we do in that case?