Transcript
0:00 hello hello how are you doing today i'm good so uh kevin so you're preparing for um the general you know fang data science interviews you know generally targeting product data science roles and so um you set up a mock interview with me so that way i can kind of give you some prep questions that can be really helpful for product data science in general right yeah um i think this is the most effective way to practice so i'm looking
0:27 forward to it yeah um so even though this is you know obviously based on facebook um the type of you know questions that we go through in this mock interview is gonna be helpful for other um you know product data science interviews whether it's like snapchat um or you know one of the subsidiaries within facebook which is like instagram or even tick tock so uh so hopefully that you're gonna find a lot of benefit to this um block interview that i'll be
0:52 conducting for you okay yeah that makes fun all right so in order to focus on this mock interview what we're going to do is we're going to go through like a product sense and i'll follow up with the sql question and along the way if you have any questions obviously you know you can definitely ask clarifying questions those sort of things okay sounds good all right so i'm going to start with the first question which is
1:15 um all right so how would you measure user turn on instagram um okay let's see so um i think first thing is a couple of cliff questions i want to clarify before we move forward um so user churn so user return usually um just means that it's the way i think about it is inactive users so do you want me to kind of define more concrete time frame for that inactivity on my own and then the type of actions that they
1:46 take so i would actually honestly prefer if you kind of um perhaps just think about the initial kind of approach and then maybe kind of work your way around this instead of okay you know telling you this is what you should be looking at first yeah of course and then um so instagram it's pretty broad um you know there are even multiple offerings within instagram for example you have the regular post you have stories you have um explore page
2:11 and then now you have a shopping tab is there any subsection within instagram that you want me to focus on uh you know what let's just say general users general user okay yeah yeah um okay so and is there can i know who um like who the stakeholder is uh are interested in this uh knowing this measurement yeah so i would say maybe like executives people who are on the higher up on the instagram chain okay
2:40 okay okay so in this case i think even before we um like uh define a very quantitative and measurable metric on this i'll kind of back up a little bit and then think about instagram as a platform so you know it's primarily a photo sharing app but it has evolved into i think a lot more than just photo sharing and generally a very useful way for people to connect with each other and then it's also um i think it's
3:07 unique in a way that the user demographic is quite different from facebook um as a whole and yeah so it's a pretty important business to instagram um so in terms of if executives want to know user trend i think they primarily want to know i would say i would guess there are two ways you can think about it right um so maybe there is just a decrease in the number of active users and then we can define the
3:36 whether how we define active later but then there's also i also want to think about kind of engagement drop so maybe the users are kind of just checking a lot less frequently so um they don't necessarily turn all the way like they're not deleting their account for example but then maybe their time now are getting pulled to other uh competitors so like you mentioned like snapchat um tick tock that sort of thing so i would also almost in a way to kind
4:05 of think about that as a user trend in a way if the engagement time is degrees a lot so i would say right now um let me try to kind of type out some notes so one is totally users and then two is then dramatic drop in so which of these do you think do you want me to focus on more i think those are very important and they kind of answer different questions for um so
4:36 okay so when you think about this for a second first of all you have to have a way to measure that before you can prioritize in the second one right yeah so let's let's now talk about more details on how to define it so totally active users i would think about this i would want to define some kind of time frame for someone to be classified as inactive okay so you can think about it as well there are
5:01 many ways right so i can think about how many times they log in say if they don't log in in a sunday period we can consider a return or we could also consider that maybe you want an even more fine grain level where they kind of log in but then they're not i guess it just relates to the second point more so they log in but they're not doing any engagement and by engagement i mean they're not liking any
5:27 posts they're not viewing any stories they're not posting they're not commenting they're not direct messaging any users so let's see for the first case i can i think the way i'll define it would be say [Music] within a seven day period say um lot number of users who have not
5:58 logged in now some day period um and then so that's a total number of um of user turn um in that seven-day period um and then for engagement i would say also within some day period for user engagement i would say
6:30 for example i guess for this one i would define a rate so total number of unique user and then which number of so this is kind of how i would uh imagine engagement so within a seven day period so what is the number average number of engagement actions so engagement actions here i'm defining as
7:03 liking commenting posting or viewing stories or or making stories or making direct messages so any kind of action other than just log in onto the platform take it during the seven days and then divide by the total number in the user so that will kind of give you average rate and you can watch how that rate changed all right so i have a couple follow-up questions starting with the sure so why seven days
7:34 so um yeah the seventh day for me is to kind of deal with things that are for example day of the week effect so for example i would imagine that maybe people would check um instagram less on tuesday than say on friday saturday on like for example on the weekend so i think kind of give you that seven day period smooth out some of this pure uh period periodic nature of uh instagram engagement okay got it um and i noticed that you
8:03 know you've essentially kind of subdivided a turn to in terms of two different buckets right yeah the case where someone logs in and then no activity and then another case where um you know basically um uh you know they uh you know they just don't engage as much anymore right um but if you had to basically just pick one as your definition for churn which one you actually pick yeah especially for uh the business holder so you know in the beginning i
8:33 asked who like the executives are the ones that are interested in this right and i think they this is much more important than drawing a drop in user engagement so the reason being that for example how instagram as a platform makes money is through advertising right so you need the users to actually be using the platform instead of you know kind of just briefly logging in and then be on the platform then they're not going to see
8:58 ads as often for example right and they're not using the if they're not engaging with the app long term um they're they probably aren't it's probably an indicator that they're not having a good time so they're not having a good user experience with the application um so this i think serves as a very very important kind of um indicator so you can kind of watch out for uh things that may go wrong before they kind of catastrophically go wrong so to
9:27 me like totally active users if they're not even logging in that's true it's very hard to pull the users back right instead of uh versus user who are right now they're on the platform by just not using your platform as much then maybe uh if we um kind of monitor this metric then we can inform the business holders that okay we're seeing this drop in user engagement then they can kind of devise new um strategies to pull it back so i
9:53 think the second one is a lot more important in terms of defining user trend okay got it and um one last question which is um sign in so would you consider sign in as like a engagement define it as engagement i think i would think about it as to me i wouldn't consider that as an engagement uh just because
10:23 you know sign in there's no there's no measurement uh for example how much time they spent on the the platform right so all the other engagement although it's not direct measurement of how much time they spent on the platform it's still an indirect measurement for the time and then also you know people usually so for example if you have the instagram app on your phone you don't usually people don't like log out and then log back in right
10:45 so i think that having that login is not it's not a very useful indicator to me okay that makes sense all right um so i'm now going to set you up and ask a question if you just give me a second okay all right so now sort of kind of following up on this um okay so suppose that um you the definition of turn here is basically if they haven't displayed any active
11:16 behavior and that is reading comment like impose the past seven days okay okay now how many users have turned as of today use a table below to address this question and i'm going to paste the table give me a second all right so here's a table as you can see you have a time stamp user id and activity in this usage table now use this table here to basically address this question which is finding out how many users are um have churned
11:49 okay okay let's see so we're reporting just the number of users trained and not in terms of dividing by the total number of users yeah that's right so we're not defined yeah it's just simple okay let me see okay so activity comment right so sign in i'm assuming this one it's yeah interesting how we tie that back into the the product question so i would assume that this is not an engagement activity is that right yeah sign it yeah okay
12:19 um so let me kind of outline so i think i would count to get the number user i would do count distinct user id um to get this thing as a number of users and then you also need to apply a filter on the activity uh and then also you need to filter out by timestamp so let's do that um so i'll kind of write it out and then i would explain kind of the way i
12:45 construct everything at the end um where um i almost did a list comprehension there like we do in python by don't be networked but so i'm gonna do this let's just suppose that um for those you know who are obviously
13:16 watching this mock interview um it doesn't matter what sql query you're using whether it's postgresql yeah you know uh or mike's sql the purpose is just to get the logic of things so it can be like pseudo sql in a sense and then you only want past seven days so between now actually no
13:52 um okay so let's see so um let's start off from here so i'm selecting from this table and then i kind of filter out based on activity so now i'm only showing that activity um i don't care about activities that are not real engagement right so for example sign in um and then still this will filter it down to only activities that are actual engagement activities and then they also have the corresponding user id um and then i'm also filtering by based
14:23 on the time span so i'm doing between now minus six days and then and now um so i'm assuming past seven days well we didn't define if we won today or not but um yeah so this would include today and then past six days so we just sum up to total seven days but if we won like 80 days you can just have one i think there is a bit of a confusion with the question um maybe i might have
14:46 accidentally said yes to something but um the question is they haven't used how many yeah this is actually the total opposite uh thanks for the qualification yeah i don't know i've made a mistake along the way i might have accidentally said yes to let me recall the fun question but yeah you need to the target focus is to figure out the turn uh let me check the logics right here
15:38 uh let's see if this is right so for example i'm checking if the activity is an engagement activity so i'm using or statements for all this so that means that for any activity that's you know belongs to this list you would um like you get at false so if any of these qualified so yes so i have four active separate activity check so if any of them fall into that activity list like this statement will be
16:11 completely on so we also need a knot right here okay so i'm doing inverse so essentially yeah this gives you all the this filters all the activities are uh engagement activities and then i filter down by time and then so this will give you kind of a bunch of roles that has different time stamps within the past seven days and then uh different users and then their activities but all the activities will be um
16:43 um engagement activity while non-engagement activity so then i simply count the distinct user id there do you see any potential flaw with this yeah let me see haven't displayed any active behavior past someday the only assumption is that all of the events of across all the users are locked but then whatever a case where user has logged in
17:13 or done activity prior to those seven period but then no longer their event is being registered in the table then how are you counting those users outside of that seven day window period see haven't displayed any activity active behavior so i thought um let's see so i thought they have to
17:44 to for them to qualify as a uh assuring the user they just simply cannot have any of these um active behavior within the fm data is that right yeah that is correct um okay but then what your what your query doesn't consider is like um a case where a user prior to those seven days were active and then no longer they're not doing any activity or didn't they didn't sign it at all
18:15 and your query at the moment doesn't count for those users oh okay actually you have pre-imposed um within the first within the past seven days and then among users who have essentially signed in um you're looking at users who have not displayed these activities and then you're doing a distinct count on them okay so upset of the population not the entire users will be considered turn okay so um let me ask a few clarified questions
18:46 so if i if i don't do any filtering on the time and then count the all the distinct user id in this table without giving me the total the entire user population yes okay um okay so let's see so maybe i'll actually do this um it'll give you about a just you know a minute to minute or two to basically
19:17 just revise the solution
19:56 okay it's my division this also because of the time constraint let me think does this work so essentially what i'm now doing is i'm counting the total number of users that we have this is what this inline does and then my attempt is to minus out any users that have any engagement activity in the past seven days so that will give you that that you'll be left with only users who are like essentially turned in the past
20:26 seven days so yeah that is that is my solution okay got it um great well thanks for these questions um and then now we're gonna segue over to the solutions portion and uh we're gonna go through the questions in the order they were asked sure all right so uh okay so let's um start by talking about the product sense questions because i had a couple notes um okay things that were good and things i think could use some
20:57 improvements um all right so the first so the general um kind of impression of how you were approaching this problem was really solid in the sense that you communicate things really well we're very clear about what is um what what it is that you're articulating um explain you provided enough details for me to understand it more often than not candidates might over explain or underexplain but in your case you had an idea and then you explained just enough for me to kind of
21:28 get your point so that's really good in terms of communication which is another a very common attribute that's being assessed or technical interview the second thing is i really like how you were asking clarifying questions um made things very engaging with the interviewer and for generally speaking for these type of technical interview um that it's best not to do like a model but kind of engage the interviewer um brainstorm ideas with this person um the more interactive it is the better
21:59 it is that helps interview you know that you um have a kind of good sense of like how do you collaborate with the person right and that is something that interviewers will kind of look for you know um so those are those are the uh two things that were really great about how you were responding to those all right so now let's go through uh some of the areas or improvements um
22:31 okay so actually one other thing that i just want to mention is that you started out in a good framework structure okay um let's have clarifying questions let's really understand what the target goal is second thing is understand the summary of you know basically the product context of the business goal right um and you did those you set up you basically set up the background before you actually dive into solution which is really good now let's talk about the solution itself
23:01 um there's a couple things that i think you do really well which is so first of all um you kind of found like what is a good proxy for measuring um you know inactive users in this case it is going to be um essentially engagement right um so you look at the engagement actions like click uh read comment you know post right um and those are those are really good um that you know you like brought up
23:33 um hey kevin hey hey sorry um there was a little bit of disconnect on the internet on our part and so we just um you know got back in and now we're gonna resume the session and going through the questions together um all right so there are okay so there are the couple things that you did really well in terms of you know how you came up with the solution so first of all
24:03 your sense about um basically how you define the um you know the churn in this case is basically enact inactivity right um and the inactive actions that you were thinking about or um you know the read the comment the posts you know you know and those sort of things right uh so that's good that you were able to be very tangible about that you know because sometimes canada will just say you know inactive or no usage right but then that doesn't mean
24:34 anything because usage can pretty much mean anything and so the more you're able to be very concrete about what is the concrete actions that a user will take you know that will define whether they're active or not um you know then that's that's good that you were able to flesh that out uh the second thing that i think you also did really well is you define a window period instead of just saying okay they're inactive which is just vague by itself you define
25:01 what that window period is you know in this case you set a seven day window period right now seven day i thought was reasonable but um but in terms of your reasoning answers why you pick seven day period was a little bit kind of lacking because okay mention the idea of um you know let's take the effect of like maybe there's like a some sort of seasonality associated to it right so maybe users might log in
25:29 um you know monday through friday but then they might not on the weekends or it might be the other around or something like that right yeah but the other aspect that i think you could have definitely considered was the fact that when you think about instagram um the way that someone design is like almost like a daily engagement kind of thing right like they have what what what's one of their core features well there's really two things two like
25:54 primary features as soon as you go on the homepage right um the first one being the stories um and then the second one would be these scrolls right and the way it's somewhat designed is i um it's like you want to be in constant loop with what your friends are doing right right yeah i agree stories get removed um in a short period of time um you know these images that you see on a feed you you rarely see a like a post
26:26 that was published seven days ago you posted you see both that were published today you know not yesterday or a couple days ago so their underlying assumption is that okay we want users to be engaged on almost on a daily basis um but then if they're not active in across the seven day period which is fairly you know long considering that assumption is like daily engagement then you know then they could be considered like a turn
26:53 um so obviously a seven day it could be like a seven day 14 day 30 day really depends on the subtlety of like what is the user turn um okay you know because they're with user turned there could be different types of problems that you're solving in general but um but that would have been a much better better reasoning in terms of why you picked that seven day period okay yeah thanks for the feedback the other aspect to this is the um
27:24 i was a little confused in terms of why you ended up picking two different definitions um because okay so so the question is how do you measure user return not how do you measure user engagement they sound a little similar but they're not necessarily the same thing um if i if i ask you know how would you measure user engagement on instagram then i think the way you define the metric which is average number of engagement actions taken during seven
27:51 day uh divided by you know whatever denominator would have made sense but then um the question is about user turn so i thought that this is a little bit superfluous in terms of what you brought up there that's why i had like a follow-up question which is like if you have to pick one which one would you pay and i was kind of hoping that you would essentially pick total total uh inactive users you know uh not necessarily engagement in this
28:17 case so that was a local mark because okay kind of imagine um this there's there's a whole data science project revolving around predicting user turn that way um instagram could you know build build models um that could prevent user turn and this is pretty much true in almost any of the you know product based company whether it's lift trying to prevent driver turn right or uh or amazon trying to prevent churn you know whether they're trying to
28:48 prevent turn by uh sending out emails push notifications whatever it is they want a clear label ones and zeros in terms of okay this user is gonna turn the next seven days or something like that right so um picking one definition um would have made sense in this case would have been the first one not necessarily the second one actually okay and um i also asked you know it's like sign in engagement um you know would you consider sign in
29:19 as an engagement and uh you may have um i agree with you that you know sign in should not necessarily be considered as an engagement because what could happen is that you know user might have accidentally just signed in but then they're not they met they might be near the exit right um so in that case there's really no activity whatsoever so it's just a little bit like a misregistration in terms of whether they would be
29:43 considered active or not and you provided reasoning behind that so that was really good okay and so in general i mean i think i think what you did really well is just like you um made a point and then you make sure you provide justification and when you provide a justification um you did it just enough to unders for the interviewer to understand and you didn't try to over explain it and that was really interesting so i would
30:08 definitely keep up with the overall framework of how you responded to this you started by asking a different question you laid out what the business and product goals are and then from there you did like a deep dive so in terms of the general structure although there were a little bit of kind of problem areas in terms of um some subtleties about you know what the user turn is overall and i thought you did a really good job uh okay with this
30:34 yeah i agree with all the feedback thank you yeah all right so now let's go through the sql question which is um uh all right so this here once again think many did a good job again just asking clarifying questions making sure you understand things um the couple things that was obviously a little problematic in the beginning maybe i might have misspoken i can't remember but um but the question is about you know how
31:05 many users turned not how many users active and you started yeah i addressed that question right um and that kind of you know you end up kind of wasting some time uh but yeah in the wrong right so just make sure you clearly understand what it is that that's being asked um and then from there dive into the solution now uh now let's think about you know your your query itself uh so this is actually not too different
31:36 from i'm gonna paste my solution real quick all right so essentially the logic of what you try to do is the following so let's take all the global users right a unique set of all the users to count them and then um let's take the users who are considered active right so you take the set subtract to buy this set and you count it
32:08 and then that's going to give you all the users that have turned yep and that's what you try to do um obviously this hurry would have um so so okay so it's not so much obviously if we were to run it then i think there might be some maybe logical like syntax errors but in terms of what the the logic of what you were going for makes sense um that's because what you're trying to do is basically get this right
32:40 yeah obviously if we're in an executable environment you have a little bit more time you would be able to polish things up you know and ultimately use the solution which i believe um but uh yeah so all in all i mean this this is um this is good good kind of sql logic in terms of you know being able to address this question which is good um now if you look at my solution um in a way it's not too different from
33:06 how you were trying to approach it so for me i started with let's take all the unique users and then this second one is to get all the users who are active and then i do like a distinct count on those users and then from there you know i i basically take these unique positions of unique users and then users from the active users past seven days um and i do a set operation which is minus
33:35 and then i count those pieces okay which would have given the result um so yeah so again you know um you know you definitely did a good job with the sql portion um and so uh so really kudos you know okay yeah thanks uh yeah that was the the hints definitely helped because i was heading into the wrong direction yeah um but but in general um i really loved how you were able to quickly make a pivot realizing that you made a
34:07 mistake um i mean really interviewers will often kind of give you some hint to make sure that you're going the right direction and they're also looking to see whether you can recover and in this case you made a very quick recovery which is really good yeah okay yeah thank you so much yeah all right well um definitely appreciate your time for this mock interview um and you know um and best of luck with the upcoming interviews for your product
34:36 data science goals yeah of course thank you so much for your time yep sounds good bye kevin bye
Summary
- User churn is defined as inactive users, with a focus on engagement metrics rather than just logins.
- Two approaches to measuring churn were discussed: total inactive users and user engagement drop.
- A seven-day period was suggested for measuring inactivity to account for user behavior patterns.
- Clarifying questions are important for understanding the problem context and engaging with the interviewer.
- The distinction between engagement and churn metrics is crucial; engagement metrics may not directly indicate churn.
- SQL logic for determining churn involves counting distinct users and filtering based on activity within the defined period.
- The mock interview highlighted the importance of clear communication and structured responses in technical interviews.