Facebook Data Scientist Mock Interview - Measure User Churn + SQL Problem

🚀Land your dream data job using datainterview.com/.
====== ✅ Details ======
🤔 "How would you measure churn on Instagram?"
Here's a mock interview based on Facebook's technical phone screen with a product-sense and SQL question. The interviewer was a data scientist at Google and PayPal, and the candidate is preparing for product data science roles at FAANG companies.
Watch the mock interview to prepare for your own product data science interview. Make sure to check out datainterview.com/ for more content!
👍 Make sure to subscribe, like and share!
====== ⏱️ Timestamps ======
0:00 Intro
01:20 How would you measure user churn on Instagram?
08:05 Choose one primary metric
02:07 Should user sign-in be considered as churn?
20:40 Product-sense solution
30:39 SQL solution
====== 📚 Other Useful Contents ======
1. Principles and Frameworks of Product Metrics | KZbin Case Study
Link: / principles-and-framewo...
1. How to Crack the Data Scientist Case Interview
Link: / crack-the-data-scienti...
2. How to Crack the Amazon Data Scientist Interview
Link: / crack-the-amazon-data-...
====== Connect ======
📗 LinkedIn - / danleedata
📘 Medium - / datainterview

[@saad1732]

this is how I was approaching the SQL statement: with cte as (select distinct user_id, timestamp, activity, current_date - DATE(max(timestamp)) as days_since_activity from usage where activity != 'sign-in') select count(*) from cte where days_since_activity > 7

[@leomiao5959]

Very good mock interview! Thank you.

[@DataInterview]

Thank you!

[@josephkim7512]

can you do a min(date) and max(date) partition by ... find the difference between the 2 dates where > 7 and that will be all the "churned users" ?

[@gtang31]

cant we use the lag window function on date partitioned by user_id and then compare that result vs today's date to figure out user churn?

[@allison-hd1fg]

For the sql question, can we first get a temporary table and then only count user id that is not in the table where they have any engagement within the last 7 days, in that case, we would get a comprehensive list of users.

[@anmol557]

I tried a similar approach for the SQl but introducing CASE statements: With master as ( Select count(distinct user_id) as total_users, count(distinct case when ( activity in (‘read’, ‘comment’, ‘like’, ‘post’) and (timestamp between dateadd(day, -7, current date) and current date)) then user_id end) as active_user From usage ) Select (total_user - active_user ) as churned From master

[@anmol557]

@@Mark-ei3bv i don’t totally understand what you are suggesting. For active users I am basically counting distinct user_id that satisfy the condition of being active. I don’t how it will ever be greater than total users. Would definitely love to get more clarity on what you are suggestion

[@ev2175]

Good video. Thanks for taking the time to make this

[@DataInterview]

Of course! - Dan

[@klintmane1672]

Hi! I am sorry to say but the second measure does not really make sense to me. # of unique users is billions. Anything will be very very very small. How can you distinguish the drop between super duper small numbers?

[@waynezhang9869]

Just a question about SQL part. If a person is active in NOW() - 8, and then remains inactive. In the logic shown in the video, this user will be in the total active user list while not in recent 7 days ([NOW()-6, NOW()] ) active user list. Hence it would be counted as a churn. But, my question is that, the problem is to find user churn of TODAY. Shouldn't that user be counted as the user churn of yesterday instead of today? Since based on the observation of yesterday, that user is already a churn. I am confused that to calculate the user churn of TODAY, should we use the user list active in [NOW()-7, NOW()-1] minus user list active in [NOW()-6, NOW()]?

[@touwmer]

No, the user is churned starting from yesterday, and he/she is still considered as churned today.

[@aravindkramesh]

*I wish I could become a Product Analyst at Google.*

[@brittosabu8401]

For the SQL Query part Can't we just find the MAX(timestamp) per user and see whether CURRENT_DATE - MAX(timestamp) >7 for a user . If it is , then the user is churned ?

[@stephenlung2595]

@@sastivel This would work too right? with current_tbl as ( select user_id, max(timestamp) as latest_date from usage where activity in (’read’, ‘comment’, ‘like’, ‘post’) group by 1) select count(user_id) from current_tbl where date_diff(day, latest_date::date, current_date()) >= 7

[@EvaristoMoreira86]

if you do select countd(user_id) as nb_users from table having max(timestamp) < current_date -7, you'd be counting those who did not use the app (didn't have any activity) at all in the past 7 days, but a user who logged within the past 7 days and didn't do any engagement activity would be considered active by this logic.

[@namandoshi4478]

Hi, why I can't I just count users where filter not in (read, write,post,like) ?

[@namandoshi4478]

OK I got it. Instead of minus, can I use left join and b.user_id is null?

[@orangethemeow]

@@namandoshi4478 I think the question should clarify what's in the activity column. Are there any nulls or is the type enum?

[@anmol557]

@@orangethemeow That is a valid questions and it is something that you clarify by asking questions to the interviewer.

[@apsaravidhya8869]

Very professional and informative. Can you do a python solution for this?

[@DataInterview]

Sure, will plan to do one in the near future!

[@coolghoul9]

do you have a video with the "optimal" answers for all questions

[@coolghoul9]

i commented prematurely

[@yuhan8343]

My SQL solution for this question： with active_users as (select date(timestamp), distinct user_id from usage where activity in ('read', 'comment','like','post') and date(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()) select count(distinct user_id) from usage where user_id NOT IN (select user_id from active_users) and date(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE())

[@JobQuestUnveiled]

SELECT COUNT(DISTINCT user_id) AS churned_users FROM usage WHERE activity NOT IN ('sign-in') AND timestamp

[@niiniiy9940]

3:00

[@suhascrazy805]

You forgot to subtract new users who just started using instagram in the past week, so the user churn from your query is under-counted

[@dangtran5766]

Thank you so much for this informative and helpful video. Can you check my solution for the SQL question? select count(t1.user_id) as churn from (select distinct user_id from `Usage` where DATE(timestamp) = DATE(NOW()) - 7 ) as t1 LEFT ANTI JOIN (select distinct user_id from `Usage` where DATE(timestamp) = DATE(NOW()) - 6 ) as t2 ON t1.user_id = t2.user_id

[@lingxu9697]

If I am the interviewer, I probably will give a 'not hiring' to this candidate... The sql question is not very hard

[@jaad9848]

This. The person doing the mock interview must have paid for the opportunity to do the mock interview because the mock interview feedback gave *way way* more benefit of the doubt that a candidate would have got unless the candidate was the son of an Exec

[@coolghoul9]

are you in a bathroom

[@JobQuestUnveiled]

[@adityagaikwad7868]

datainterview.com v/s stratascratch ?? anoyone?