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
@nav6413 күн бұрын
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.
@anmol5572 жыл бұрын
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
@anmol5572 жыл бұрын
@@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
@josephkim75122 жыл бұрын
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" ?
@aravindkramesh2 жыл бұрын
*I wish I could become a Product Analyst at Google.*
@brittosabu84012 жыл бұрын
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 ?
@stephenlung25952 жыл бұрын
@@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
@EvaristoMoreira862 жыл бұрын
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.
@leomiao59592 жыл бұрын
Very good mock interview! Thank you.
@DataInterview2 жыл бұрын
Thank you!
@suhascrazy8052 жыл бұрын
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
@waynezhang98692 жыл бұрын
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()]?
@touwmer2 жыл бұрын
No, the user is churned starting from yesterday, and he/she is still considered as churned today.
@klintmane16722 жыл бұрын
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?
@allison-hd1fg2 жыл бұрын
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.
@gtang312 жыл бұрын
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?
@apsaravidhya88692 жыл бұрын
Very professional and informative. Can you do a python solution for this?
@DataInterview2 жыл бұрын
Sure, will plan to do one in the near future!
@yuhan83436 ай бұрын
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())
@namandoshi44782 жыл бұрын
Hi, why I can't I just count users where filter not in (read, write,post,like) ?
@namandoshi44782 жыл бұрын
OK I got it. Instead of minus, can I use left join and b.user_id is null?
@orangethemeow2 жыл бұрын
@@namandoshi4478 I think the question should clarify what's in the activity column. Are there any nulls or is the type enum?
@anmol5572 жыл бұрын
@@orangethemeow That is a valid questions and it is something that you clarify by asking questions to the interviewer.
@JobQuestUnveiled9 ай бұрын
SELECT COUNT(DISTINCT user_id) AS churned_users FROM usage WHERE activity NOT IN ('sign-in') AND timestamp
@ev21752 жыл бұрын
Good video. Thanks for taking the time to make this
@DataInterview2 жыл бұрын
Of course! - Dan
@coolghoul92 жыл бұрын
do you have a video with the "optimal" answers for all questions
@coolghoul92 жыл бұрын
i commented prematurely
@niiniiy99402 жыл бұрын
3:00
@lingxu96972 жыл бұрын
If I am the interviewer, I probably will give a 'not hiring' to this candidate... The sql question is not very hard
@jaad98482 жыл бұрын
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
@dangtran57662 жыл бұрын
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