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 ?
@@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
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.
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
Hey I think its a good attempt on the query, but I believe there might be an oversight in the WHERE condition. By excluding users who have performed the 'sign-in' activity, we risk filtering out a significant portion of relevant user IDs, potentially skewing the churn analysis. It's important to consider whether 'sign-in' should be classified as an indicator of activity to ensure we're accurately capturing user engagement.
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()]?
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
@@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
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?
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())
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.
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
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
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 ?
@@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
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.
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
Hey I think its a good attempt on the query, but I believe there might be an oversight in the WHERE condition. By excluding users who have performed the 'sign-in' activity, we risk filtering out a significant portion of relevant user IDs, potentially skewing the churn analysis. It's important to consider whether 'sign-in' should be classified as an indicator of activity to ensure we're accurately capturing user engagement.
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" ?
*I wish I could become a Product Analyst at Google.*
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()]?
No, the user is churned starting from yesterday, and he/she is still considered as churned today.
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
@@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
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?
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())
SELECT COUNT(DISTINCT user_id) AS churned_users
FROM usage
WHERE
activity NOT IN ('sign-in') AND
timestamp
Very good mock interview! Thank you.
Thank you!
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.
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?
Hi, why I can't I just count users where filter not in (read, write,post,like) ?
OK I got it. Instead of minus, can I use left join and b.user_id is null?
@@namandoshi4478 I think the question should clarify what's in the activity column. Are there any nulls or is the type enum?
@@orangethemeow That is a valid questions and it is something that you clarify by asking questions to the interviewer.
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
are you in a bathroom
do you have a video with the "optimal" answers for all questions
i commented prematurely
If I am the interviewer, I probably will give a 'not hiring' to this candidate... The sql question is not very hard
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
3:00
Very professional and informative. Can you do a python solution for this?
Sure, will plan to do one in the near future!
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
Good video. Thanks for taking the time to make this
Of course! - Dan
datainterview.com v/s stratascratch ?? anoyone?