Facebook SQL Mock Technical Interview: Part 2

[@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

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

[@xinchenlu6385]

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

[@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

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

[@brothermalcolm]

I like how this felt real and not scripted

[@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

[@ravitanwar9537]

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

[@bindidesai2191]

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?

[@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

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

[@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

[@bhumikasoni361]

You guys are doing a wonderful job in job preparation. Just one request, if you could increase volume of videos from you side it would be great as even at the highest volume in my system, the sound is a bit low. Thanks!

[@iqjayfeng]

We'll definitely keep that in mind. Thanks!

[@r-rk]

Link for part 1?

[@bharadwaj2008]

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

[@arojitdas8256]

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

[@DSISketchGurl]

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

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

[@kloveinn]

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

[@GuillermoBenjamin]

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

[@ben-9200_]

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

[@SagarKumar-db2xy]

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

[@jsaaiman]

Nice one!

[@iqjayfeng]

Thank you! Cheers!

[@divyne_]

hey jay, thanks for the amazing content. can you post any videos for data engineering 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.

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

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

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