transcribe

Facebook Data Scientist Interview - Measure Marketplace

DataInterview · 36m · transcribed May 2026
More from DataInterview Business
𝕏 Share ▶ YouTube 📥 PDF 🤖 .md

Transcript

0:00 hey everyone this is dan the data scientist interview instructor from datainterview.com in this video we're going to watch a 30 minutes of mock interview based on facebook's product data science round now as some of you know facebook loves to ask product sense and escort questions in their interviews so what we're going to do is we're going to take an actual candidate who is preparing for interviews at fang companies and grill him with some sql and product sense

0:26 questions now we're not going to really cover the solutions part here because that's actually part of the monthly subscription so you should definitely check out dataview.com if you want to you know look take a look into how to actually solve these problems but nonetheless this is the 30 minutes the first portion being sql followed by the product sense and as you're going through the video you're going to see on the on the right side of the quarter pad the

0:51 interviewer's commentary which is actually written by me and you will be able to see how the interviewer is evaluating candidates response to these technical questions so without further ado enjoy the video and make sure you check out dannyview.com please share this video and like this video and if you have any questions or comments um drop a comment down below enjoy hello hey dan how are you doing i'm good are you doing good yeah thanks

1:23 for taking the time yep yeah so welcome to the mock interview for the facebook product data scientist um interview this is kind of mimic games with their phone screening and it will cover two sections of product sense and sql well in fact actually start with product uh sorry sql which is going to be about um 20 minutes and then the product sends can be about 20 minutes and then we'll spend the remaining time of the hour

1:51 just going through the solutions together does that sound good with you yeah that sounds good to me okay awesome all right so on the quarter pad um i've pasted the sql um uh basically tables and and i'm gonna give you two questions for you to answer all right so i'll start with the first question oops all right so based on the user activity and post reactions table

2:22 can you count the percentage and count of each user activity publish comment upload per day and i just want to mention that the uid field and the post reactions is a uid of uh basically the person that posted it so uh yeah let me take a couple minutes and take a quick a moment to read through the um all the columns in each of the tables okay

2:53 so on the user activity we have four names uid which is the um foreign post reaction table and activity and the date and the post reaction uh we have post id as reactions uid so and base so the first question is to get a calculated count and the percentage of user activity um as the desire um what is the

3:24 um the expected absolute guy is um i mean so it did uh output books will be probably another okay so expiry output is um um by each stage and and for each activity we will gather like activity number and activity um

3:57 [Music] percentage okay sounds good um so i think um the first step i will do is to calculate the number of the uid per for each activity and by date okay

4:27 then i will the next i will divided the numbers of the counts by the total number of the uid per day to get the percentage okay sounds good um okay so select from user activity from user fqt so given the desired outcome um so i can um just go by date and then the

5:00 activity activities and then count the um count the uid so just want to be clear so you there's no duplicate uid on user activity table it's unique it's distinct on uid one user you can have yeah so one user will get one uid um but of course given that this is

5:33 like kind of like an action table where you have user activity you can have multiple instances of uid appear okay gotcha let's see um so um i asked another quick question so if okay so if the say john doe has the uid and the activity is published yeah i got shot okay so why use the camera for your id yeah oh sorry that makes sense to me thank you um so

6:08 group by one and two so i think this one will give me the activity uh count and then

6:44 um and then basically um so here our best tea i needed but this one i will select i will divide it uh i owned by the um select um expect some of accounts basically just like have a

7:16 supper to get what the total activity counts from the previous table we graded and then this will be our denominator and um but i will have will we need to get a percentage so i think we should times hundreds um i think for the postgrads sometimes you will just like round it to the integer um so it's no harm to

7:49 um okay that's the sorry indent oh what um yeah let me briefly check whether that'll sound obvious mistake uh first that we just click on the account and next i just um divided the number of count by the total number of the account account activity count okay um yeah did you have any

8:22 suggestions or we are unable to execute the code oh yeah no so this is not an executable environment um and uh the syntax doesn't matter too much in this case i just want to understand your logic here okay so what do you think is can you just sort of walk through from beginning to the end and how what what would the output look like if you were to run this code actually so um um the first step

8:51 um the first the first step i just like agree aggregate the like active routine count for each for each so basically that's the output of the the inner city table yeah like by eight activity i got a number of counts so do the aggregation first

9:23 and then the next step um use the subquery to calculate to compute percentage so the output of the first ad just return the account for each activity and for um enough that's an issue all right yeah uh yeah because i

9:53 i always should treat the data so i think um here um i should use the window function to get the what is the total number count for each day so here i will sum over partition by um t date and then

10:25 order by you need to open no no and asked photo counts and i here um replace this one um does that make sense okay total counts for each day

10:57 and then start and then and then computing the and then come out to relating the percentage uh since we cannot uh since we cannot run the globe like um some the

11:27 count in i i think there shouldn't be an issue in rap shift like basically we can use the the column we just created and then do another calculations um yeah does that make sense to you okay yeah it sounds good um we'll just move on to the next

11:58 problem okay yeah yeah all right so now among users who published the past who published the past seven days how many posts received more emojis than likes okay so among user who published the tech post okay for publisher post seven day alert has published the past seven days how many posts receive more emoji than likes

12:29 so um so uh i want the first i want to know the expanded um outcome look like so um that would be helpful for me to uh think through the product the logic so uh how many posts we see more so basically just a number like how many posts receive right okay yeah i see um [Music]

13:01 okay so that would be so our target is the older um ammo users our condition is the user who published the past seven days so um so i think the i think my thinking person is we we treat the post reaction as the uh like our name table and we will join the user active table to

13:33 filter out our user not meet this condition with this criteria okay um so from the post reaction table and then since the um i'm using my last join the user activity

14:07 user activity okay um um we you may you already mentioned the uid is the key so and here i will also put the um uh what what is the condition okay publish the past so there are actually

14:38 two condition the first is the activity should be post yeah which is reflected by just publish in this case yeah and another [Music] another condition is the date range so the past seven who published the past

15:11 seven days so um is um greater than uh current date yep that's fine yeah yeah okay so that is absolute all the condition the next step i um i will um so i will read how many posts so in a post reaction

15:44 count the post id as the number of posts but like here we i i think we can use the having um basically just um i think [Music]

16:20 um i just use the reactions okay uh i think i i know i think okay i think i should take a step back um so basically is

16:53 this when we like joined the user active tables uh i it was just narrowly down to a post that got published the past seven day so i think here i will um for the reaction i want to know what's the number of the cost for each of the reactions and then goodbye group by one so um

17:24 you know okay so suppose i do will have multiple records right yeah um reaction like so i think not smart okay then because we are we just focus

17:55 on like emoji so in what's the okay so um if

18:26 on folder first time table will give me the total number of the ideas to okay so so for the first step you will give me the um what what's the number of likes and emojis uh each post received so um

18:58 yeah and so the next step i will click use the case one oh um you have to figure out a way to do the comparison right yeah yeah that's true um okay so

19:29 being like reference equal to so basically i don't necessarily need this one so reaction so basically just give me what's the number of the emotion like this when reaction um equal to emoji then then one else zero and and this this will be um my day eat more

20:01 juice count and do the same thing for um like okay so basically for each um that's how so yeah we don't necessarily need this line but um so i think the next step will be um just uh return how many posts um foresight they

20:36 um from the previous tables where the oh like okay like so we see more emoji than night so basically just um let me see um so

21:09 my um let me let me briefly look through my uh my process my thinking process first step i i use the post reaction as the main table and i've joined the user activity because we just cared about uh the like or mono users who power the past seven days and now i apply um these filters and also um um filtered filter down to uh publish activities and then join us to

21:41 table based on uid and then so for each of the post id i have the i can sound up what's the number of the uh reactions um which was received and that's it will return um the number for the post id and the next the final and the last step uh i just count the how many posts i did that meet our criteria which is the uh emoji count is not is larger than oh sorry light

22:12 count yeah so okay yeah all right sounds good and um so that's the end of the sql portion and then now we're going to segue over to the product sense so i'm going to change this into just regular plain text all right so

22:44 in this products sense portion um i want you to imagine that you're a data scientist working in facebook's marketplace and i want you to be able to kind of address a couple key questions that involve the business of the facebook marketplace so starting with this question which is how would you measure success on facebook's marketplace so um before we jump into the conclusion i have a couple of parent terrifying questions okay um the

23:15 first question i have is uh why what what is the business purpose of measuring the success on facebook marketplace uh why uh how how does that iterate a bit mission or business objective yeah yeah so we just want to be able to gauge the growth of the marketplace um whether it's helping the facebook platform as a whole um and um really

23:48 we want to be able to have some success metric as like kind of like a north star for um our business stakeholders dollar trap and i see so uh support the use user base and then also on combat with the uh company's goal i think this business the world use base and i will also um

24:19 business impact is to um come up with a metric metrics for stakeholders um i was like for those new stakeholders um holders to measure um the success i also um because i can also imagine that why it's important to grow the user base because uh

24:50 facebook is the mission is to build the community build the community and bring everyone closer and then by growing the use space uh you uh by increase the um um use space and also increase the engagements that will lead more users spend more time on our platforms and engaging with other uh users uh is which will in return um that we can generate more revenue but advertising post or um

25:23 like um for uh that's where how we make money so that's why that's very important to um our goal to survey and improve the engagement so okay yeah i think that's pretty clear like why we want to uh working on this analysis beforehand yep thank you um so the next step i will bring storm um some brainstorm the metrics uh that will help to measure the success

25:55 um i think the first um i think we can we can we can um apply using some of the framework to brainstorm the matrix on one um let me product brand uh matrix matrix so we can either use the aar model which like for each category we can uh come up with some the uh like trades

26:29 with this um framework so acquisitions i will i can think about on like um i get a number on number of the uh number of the users who use the marketplace and then um i mean let me write it down like a number of users who would use these features and also um like um

27:02 sometimes for some apps we also measure the number of downloads on about the sign off um but um because i will imagine that for some users they they download facebook if they already have some purpose that i just want to sell stuff and it's my first time to download on the app so i will also see what's the like a downloads and

27:33 yeah so that's for acquisition i think and another one is the activation um so i mean in order to make sure the engagement and success facebook marketplace we also not just that people like people download we also need to um track uh what's their um like a uh how frequently they um they logged in and then they use the features yeah

28:03 so for um logging and so actually i should pull this one as the second okay yeah and the thing um next pack is the three change [Music] i think the most important category

28:33 because we care about we care a lot about the engagement so um um usually i will um um i will see what's the dau or um daily active user weekly active users or monthly active users of course we need to have the definition of what activities of the user would be it can be defined by uh how how many listing a seller post

29:06 or um it can okay it can be measured from two from two perspective ones from the you from the buyer the other one is from the seller and then for each of the uh segment we can see what's the we have we can have different definitions for daily user but that's the okay um like that yeah okay and after that i would also yeah sorry do you have any no keep going i'm sorry

29:39 and then other than the like just the daily it user other than just acting user i would also interesting to know the stickiness of the uh like like in facebook we also have some ratio like diu uh diu mau ratio which is measure how um what's what's the proportion of the monthly ad user are active on a daily basis so that's why i think it's also um

30:12 helpful to know yeah so uh and then the next day is the revenue um so it is basically just um like a um uh success on every this is where we uh make the money so oh oh um okay i cannot as uh um i a quick current clarifying question so for for the marketplace is always customer to customer or the business can

30:45 also post the um like a credit posting to sell their products yeah let's just say for the sake of um exercise it's just customer to customer customer to customer okay gotcha so uh in this case uh i will just focus on customer to customer then the way we generate the money is by um um like a when they engage in the marketplace we will uh show we'll display some of the ads on the on the website and then um that's

31:16 how we um get the money from advertiser so i will in this case i will measure like average amounts we buy users and then yeah and then total um and total revenue and revenue revenue average

31:47 the average number earned generated by user so yes and i may think about other revenue well um will we um charge a seller uh like a fees when a transaction is complete or um yeah so let's just say that for uh for this example there's actually no like service fee that is charged out to

32:18 the seller percent okay gosh so in that case we won't make money by increasing the number of transactions but like more in transaction means like more people using keep plays and we can still earn the money by displaying the advertisements post okay yeah so uh i think revenue i think that last bucket is a referral um um for this case maybe it's not

32:51 that um important to measure a referral but like we can know um like i i realize for some um company when they jump into the marketplace area they will uh like a uh send out invite if you refer the friend to have the first post or sell the first product you will get like a five uh dollars of discount so in this case i will um just like how many invites and just for simply for the sake of

33:22 simplicity and then of invites set and yeah so yeah i think it's just like um the briefly uh metrics um what i will be uh keep tracking okay and i think yeah i think the next step is after we agree on which metric is we are going to prioritize

33:53 uh we need to think about what's the unit of the analysis because right now i didn't put any like time frame for each other metric so um like for example like a average dollar per user are we talking about per month per day or per week so um that would be the next time to refine the metrics okay so um along that line of what you just said there um suppose that you have to pick one

34:25 metric to measure success um then what which one would you pick here and what what would be the time frame and why so um so the next question just pick one no style matrix in what time frame okay um let me take a bath increase the special success

34:56 of um i will pick because you uh we mentioned our business goal is one of the main business goals is to grow the user base so um daily active users um can serve as proceed for um um for engagement measurement and also

35:26 um um help us measure the success of the market so i will use the um daily iq search and i will choose the actual user and then um so the definition of the active users is um the new users on plaster recurring users oh and then minus the churn users

35:59 so let's that's the formula of the active users and yeah so to my understanding um for what time frame i think we can track daily active user on actually user based on daily weekly and monthly so that's a time frame repeat yeah okay got it all right so that is the end of the um q a portion of this interview

Summary

In this mock interview video, Dan, a data scientist interview instructor, conducts a simulated interview with a candidate focusing on Facebook's product data science role. The session covers SQL and product sense questions, providing insights into the candidate's thought process and problem-solving approach without revealing the solutions, which are available through a subscription service.

- The interview is divided into SQL and product sense sections, each lasting about 20 minutes.
- SQL questions involve calculating user activity percentages and comparing post reactions (emojis vs. likes).
- The candidate discusses the logic behind SQL queries, including aggregation and filtering based on user activity.
- In the product sense section, the candidate is asked how to measure success on Facebook's marketplace.
- Key metrics discussed include user acquisition, activation, engagement (DAU, WAU, MAU), and revenue.
- The candidate emphasizes the importance of daily active users as a primary success metric for measuring engagement and growth.
- The discussion highlights the need for clear definitions and time frames for metrics to effectively track success.
- The interview also touches on the importance of aligning metrics with business objectives and stakeholder needs.
© transcribe · For agents Built with care and craft by Gokul Rajaram