SQL Interview Questions Part 55 | FACEBOOK/META Interview Question & Answer | Active User Retention

SQL Interview Questions and answers Part 55 | FACEBOOK / META SQL Interview Question And Answer | Active User Retention

This question has been asked in FACEBOOK / META interview. This question will be helpful in cracking any SQL interview

Problem Statement : Assume you have the table below containing information on Facebook user actions. Write a query to obtain the active user retention in July 2022. Output the month (in numerical format 1, 2, 3) and the number of monthly active users (MAUs).
An active user is a user who has user action (‘sign-in’, ‘like’, or ‘comment’) in the current month and last month.

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :

*Twitter:
-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------

Create Table user_actions (
users_id int,
event_id int,
event_type varchar(50),
event_date datetime )

Insert into user_actions Values(445, 7765 ,'sign-in', '05/31/2022 12:00:00')
Insert into user_actions Values(445, 3634 ,'like', '06/05/2022 12:00:00')
Insert into user_actions Values(648, 3124 ,'like', '06/18/2022 12:00:00')
Insert into user_actions Values(648, 2725 ,'sign-in', '06/22/2022 12:00:00')
Insert into user_actions Values(648, 8568 ,'comment', '07/03/2022 12:00:00')
Insert into user_actions Values(445, 4363 ,'sign-in', '07/05/2022 12:00:00')
Insert into user_actions Values(445, 2425 ,'like', '07/06/2022 12:00:00')
Insert into user_actions Values(445, 2484 ,'like', '07/22/2022 12:00:00')
Insert into user_actions Values(648, 1423 ,'sign-in', '07/26/2022 12:00:00')
Insert into user_actions Values(445, 5235 ,'comment', '07/29/2022 12:00:00')
Insert into user_actions Values(742, 6458 ,'sign-in', '07/03/2022 12:00:00')
Insert into user_actions Values(742, 1374 ,'comment', '07/19/2022 12:00:00')

#METASQLQuestions #METAInterview #METASQLInterview #FAANG #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #MAANG #sqlInterviewQuestionsForTesting

[vijay.s-llyq]

with cte as
(select month(event_date) event, users_id, event_type from user_actions
where event_type in('like', 'comment')
group by users_id, month(event_date), event_type),
cte2 as
(select *, lag(event, 1, event) over (partition by users_id order by event) as lag_month from cte),
cte3 as
(select *, (event-lag_month) sds from cte2)
select event, sum(sds) as user_count from cte3
group by event
having sum(sds) <> 0

[duggalsunidhi]

I have tried this solution :

select b.month, count(distinct a.users_Id) as retained_users
from
(select distinct
date_trunc('month', event_Date) as month,
users_id
from #user_actions
where event_type in ('sign-in', 'like','comment')
) a
left join
(select distinct
date_trunc('month', event_Date) as month,
users_id
from #user_actions
where event_type in ('sign-in', 'like','comment')
) b on a.users_id = b.users_id and a.month < b.month -1
where b.month is not null
group by 1
;

[dhyeypatel]

with cte as(
select users_id, month(event_date) as months
from user_actions
where event_type in ('sign-in', 'like', 'comment')
group by users_id, month(event_date))

select count(a.users_id) as MAUs, b.months as [month]
from cte a
join cte b
on a.users_id = b.users_id and a.months = b.months - 1 and b.months = 7
group by b.months

[subhashreekarmakar]

select count(distinct users_id ), datepart(month, event_date)+1 as month from user_actions where
users_id in
(select users_id
from user_actions
where datepart(month, event_date) = 7 and event_type in ('sign-in', 'like', 'sign-in'))
and datepart(month, event_date) = 6 and event_type in ('sign-in', 'like', 'sign-in')
group by datepart(month, event_date)

[arunkumarn]

with cte as
(
select users_id, as cnt from user_actions where month(event_date) between 6 and 7 and year(event_date) = 2022
group by users_id
having = 2
)
select 7 as months, count(users_id) as monthly_active_users from cte

[bishwarup]

with cte as(
select a.users_id, a.event_type, month(a.event_date) as event_month, month(b.event_date) as next_event_month from user_actions a
left join user_actions b
on month(a.event_date) < month(b.event_date)
and a.users_id = b.users_id)
select max(next_event_month) as months, count(distinct users_id) as MAU from cte
where next_event_month>=6 and event_month >5
order by 1;

[VarunKumarks-iy]

let's say if you have this kind of requirement in real world. specifying current month manually is not approach. instead of that we can use current_month or date function to get the current month than filtering is better

[Terminator-iqkb]

Can you share some source for SQL questions that are asked in product based companies? Please please 🙏

[jhotkins]

Hey Sunil, I think your contents are helpful, but your Subscribe tone is so annoying and interrupting my study. Can you please change them? Thanks!

[reachrishav]

SELECT
month(a.event_date)
, count(distinct a.users_id) as MAU

FROM user_actions a
JOIN user_actions b ON month(b.event_date) = month(a.event_date) - 1
and a.users_id = b.users_id
WHERE month(a.event_date) = 7
GROUP BY month(a.event_date)