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

Подписаться 30 тыс.

Просмотров 34 тыс.

50% 1

🚀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 | RU-vid 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

Наука

Опубликовано:

3 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 37

@saad1732 2 года назад

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

@anmol557 2 года назад

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 2 года назад

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

@leomiao5959 2 года назад

Very good mock interview! Thank you.

@DataInterview 2 года назад

Thank you!

@aravindkramesh 2 года назад

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

@allison-hd1fg 2 года назад

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.

@gtang31 2 года назад

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?

@josephkim7512 2 года назад

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" ?

@ev2175 2 года назад

Good video. Thanks for taking the time to make this

@DataInterview 2 года назад

Of course! - Dan

@brittosabu8401 2 года назад

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 2 года назад

@@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 2 года назад

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.

@yuhan8343 5 месяцев назад

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 9 месяцев назад

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

@klintmane1672 2 года назад

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 2 года назад

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 2 года назад

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

@apsaravidhya8869 2 года назад

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

@DataInterview 2 года назад

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

@suhascrazy805 2 года назад

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

@coolghoul9 2 года назад

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

@coolghoul9 2 года назад

i commented prematurely

@namandoshi4478 2 года назад

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

@namandoshi4478 2 года назад

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

@orangethemeow 2 года назад

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

@anmol557 2 года назад

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

@niiniiy9940 2 года назад

3:00

@dangtran5766 2 года назад

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 2 года назад

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

@jaad9848 2 года назад

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 2 года назад

are you in a bathroom

@JobQuestUnveiled 9 месяцев назад