Facebook SQL Mock Technical Interview: Part 2

Today I'm interviewing Ben, a DoorDash data scientists on a classic Facebook SQL interview question.
Here is the question we're tackling: www.interviewquery.com/questi...
Want to be featured in the next mock interview video? Apply here: airtable.com/shrdQrwKK7xxGLm6l
👉 Subscribe to my data science channel: bit.ly/2xYkyUM
🔥 Get 10% off your next interview prep: www.interviewquery.com/pricin...
❓ Check out our SQL course here: www.interviewquery.com/course...
🔑 Get professional coaching from Ben here: www.interviewquery.com/coachi...
🐦 Follow us on Twitter: / interview_query
Follow Jay on:
Twitter: / datasciencejay
LinkedIn: / jay-feng-ab66b049
Related Links:
Facebook Data Scientist Interview Guide: • The Facebook Data Scie...
Facebook Data Science Interview Questions: www.interviewquery.com/blog-f...
SQL Case Study Guide: www.interviewquery.com/p/sql-...
SQL data science interview course: www.interviewquery.com/blog-s...

[@iqjayfeng]

Hey guys! Check out our latest 2021 ultimate SQL interview guide: www.interviewquery.com/blog-sql-interview-questions/

[@hariharanswaminathan1052]

Hey I watched many of your videos recently while I was preparing for my job interviews.I have to tell you that your videos really helped me navigate through some of the interviews.You are doing a great job.Please keep it going bud ! Thank you

[@Fubler]

I think you need to move the date filter to a case in your sum functions if you want total impressions for the campaign.

[@arojitdas8256]

Will be waiting for more such interviews....GreatWork

[@ravitanwar9537]

hey jay amazing stuff, can you also come up with more python interviews like this ? like the google one you did

[@xinchenlu6385]

Another way to look at it may be to use the window function and sum the overall impressions before that day.

[@divyne_]

hey jay, thanks for the amazing content. can you post any videos for data engineering questions?

[@brothermalcolm]

I like how this felt real and not scripted

[@swativish]

Hi can you consider doing some videos on how people with the unconventional educational history (non BS or non MS) who are working as data analyst/engineer but find it hard to find newer opportunities

[@ParasDoshiBlog]

There is a point in the interview where you were wondering about whether the where clause is applied to windows functions or not. The answer is "yes" since the where clause is applied first in the order of operations before window functions are applied. To see this in action, run the queries below: -- without windows function SELECT date, (home_goal + away_goal) AS goals, (SELECT AVG(home_goal + away_goal) FROM match WHERE season = '2011/2012') AS overall_avg FROM match WHERE season = '2011/2012'; -- with windows function SELECT date, (home_goal + away_goal) AS goals, AVG(home_goal + away_goal) OVER() AS overall_avg FROM match WHERE season = '2011/2012'; since the results is the same, we can conclude that we don't have to put a where clause again for the windows functions.

[@sprihagupta11]

Right but he wanted to see all the total impressions (not just the last 7 days) so the where would be applied first and that filter out all the other data.

[@satz611]

Alternatively, you can also calculate the weekly average until the current date to see the trend of the campaign. The problem with your daily_average calculation is, it assumes there would be impressions every day and that doesn't account for the holidays (no activity days). pseduo code: sum(impressions) over(partition by campaign_id order by dt rows between dt- interval '6 days' and dt) as campaign_weekly_average

[@dongyan8717]

During these live coding interviews are you allow to run your code to see results as you build the query? That makes a big difference for me as I solve

[@bharadwaj2008]

Dude you look so much like richardyuzee, mind blown!!

[@ketkarah]

Hi, in the above main query can't we use sum(num_impressions)/c.target_impressions instead of avg(total_impressions)/c.target_impressions, as we are grouping by campaign_id anyway ?

[@sptknry]

If you use sum, you would have 7 times the actual total_impressions for by that campaign up to that point. Because remember the main query has 7 rows per campaign for each of the 7 days. And each of those 7 days still has the same number for total_impressions (constant - as it is just the total impressions for that campaign from start of campaign until current_date). So the average is actually just to divide by 7 and get back that constant number since you have to apply an aggregate function there.

[@r-rk]

Link for part 1?

[@vinhthekid_mt]

Super helpful. Would the interviewer allow you to use python as well, or just SQL?

[@claudiohi]

Yes, they clarify usually that you can use R, SQL or Python for this technical portion.

[@DSISketchGurl]

What level of difficulty is this question? Like, is this for more of a junior, mid-level, or senior role?

[@kloveinn]

Hard to see ! Maybe remove the other of markdown notes ?

[@rodrigo_benitez]

Coding live interviews are simply crazy

[@MrMLBson09]

glad I'm not the only one. I can't think straight when there's a spotlight on me. luckily there are plenty of companies that either don't do this, or have very reasonable/simple enough questions

[@davidoh6342]

in order to get daily average, don't you need to divide the number of days that the campaign was actually active? and not just hard coded 7?

[@fruitygranulizer540]

no, he had a where function that only took the last 7 days. he made the reasonable assumption that the campaign has been going for at least 7 days, and only selected the last 7 days.

[@GuillermoBenjamin]

AVERAGE (CASE WHEN BILLDATE BETWEEN BILL_DATE -7 AND BILLDATE THEN AMOUNT END) OVER (PARTITION BY CUSTOMER) AVG_7_DAY

[@mmmtee00]

It would be great if the interviewer can give some overall feedback at the end, like if the interviewee did a good job, or points of improvement.

[@bindidesai8564]

An attendance log for every student in a school district attendance_events : date | student_id | attendance • A summary table with demographics for each student in the district all_ students : student_id | school_id | grade_level | date_of_birth | hometown Using this data, you could answer questions like the following: • What percent of students attend school on their birthday? • Which grade level had the largest drop in attendance between yesterday and today?

[@benkivuva2279]

I am going to fail at my interview so bad there is no point of even showing up 🤣🤣🤣🤣🤣🤣🤣

[@monikazad8731]

Hey.. I was asked for MySQL Language interview at facebook. So that means my interview will be in SQL language right?

[@henalipatel]

Was the fb interview in SQL language, and how were the questions? (I.e. did they ask about explaining what a specific function means where vs having etc or doing just queries?) Thanks!

[@ashleygarland781]

@Henali Patel did you have a sql interview? If so how was it?

[@SagarKumar-db2xy]

I would fucking shit my pants and cry when I see such queries. M just beginner but yeah..

[@prachimahapatra6549]

White background will be better

[@fruitygranulizer540]

no it wont

[@sirulrich88]

That they still do interview questions in SQL seems outdated. I am consulting for companies in the forefront of data science and havent used SQL for years. Everyone uses some solution with Spark logic.