Solving a SQL Interview Question by FACEBOOK | (Data Science Interview) SMS Confirmations From Users

This SQL data science interview question was asked by Facebook. I’ll cover both the question and answer and give a detailed explanation of the approach. I walkthrough each step of my answer, assumptions, approach, and explain every line of code I write. This is literally how I would answer every data science interview question and prepare for every data science interview at FAANG companies and others.

The question has multiple steps including applying a LEFT JOIN to multiple database tables, using a NOT IN to filter out rows based on values you want to exclude in the final answer,, and using the SQL count() function to help calculate a percentage. This question covers concepts that are commonly found in data science interviews at Facebook and Google.

______________________________________________________________________

______________________________________________________________________
Timestamps:

Intro: (0:00)
Question: (0:14)
Exploring the Data: (0:57)
Writing Out The Approach: (2:32)
Coding The Solution: (5:14)

______________________________________________________________________
About The Platform:

I'm using StrataScratch, a platform that allows you to practice real data science interview questions. There are over 1000+ interview questions that cover coding (SQL and python), statistics, probability, product sense, and business cases.

I created this platform because I wanted to build a resource to specifically help prepare data scientists for their technical interviews and to generally improve their analytical skills. Over my career as a data scientist, I never was able to find a dedicated platform for data science interview prep. LeetCode and HackerRank were the closest but these platforms specifically serve the computer developer community so their questions focus more on algorithms that working with data.

______________________________________________________________________
Contact:

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

[hiovanycubillosgomez]

Your initial analysis before you to start to write t-sql is excellent, because sometimes we never reading well the statement, the besth teacher Nate, thank you so much for this.

[madsanty]

Hey, i love your work i hope you continue making videos even the audience isnt a lot, the people who follow you really loves and understand the value of this info. I love all your series and hope you dont lose your interest in making videos. Thank you!!!?

[ranjitsingh]

Loved the initial break down of the problem before you started writing the query. Thanks.

[roma]

I had my first interview with Meta today, and while one of the questions you discussed came up total percentage, not an exact version, but similar), I did struggle through the process. However, I received the right input. Unfortunately, I didn’t land the role.

[petertao]

Thank you so much! Great resource!
Would you please making some interview videos for potential data analysts?

[Adam-mwhu]

Nate,

Why would you not include "confirmation" message type? It seems like those are exactly the ones we're interested in. Confirmation is the first step, the second step is for the user to confirm the confirmation. Calculate the percentage where the confirmation is confirmed.

The other types of communication ("message" and "friend request") would be something like "you have a friend request" or "Janie posted on your wall" and should be filtered out.

[lingxu]

I feel this questions is easy to code out, but hard to get your head around about the denominator and numerator...

[ohh_nina_nyc]

Problem like this always needs a long time to understand the meaning. Even though it is not hard at all

[kloveinn]

why did you filter out freind_request? shouldnt that be part of "total texts" sent on that day? Its a small thing but just curious.

[aashi]

I got confused with Invalid confirmation. I was thinking that there are mix of valid and invalid confirmation types. particularly this line -'Confirmation texts are only valid on the date they were sent.' (means message+valid confirmation(after join) will be valid) .This will give the result(11.14 %).I guess, I get confused(or overthink) with understanding the language of these question. How to avoid this trap! Mostly happens for many Facebook questions.