FACEBOOK/META Interview Question Solved - SQL | 'Active User Retention' | Everyday Data Science

In this video I solve and explain a DataLemur SQL question using both PostgreSQL query. These questions will help you practice for FAANG or MAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

Datalemur is one of the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#DataLemur #sql #datascienceinterview

[Art.of.the.Warriors]

You can also do it using a window function:

with cte as (
SELECT
user_id,
EXTRACT(month from event_date) as month,
lag(EXTRACT(month from event_date)) over (PARTITION BY user_id order by event_date) as prev_month
FROM
user_actions
WHERE
EXTRACT(year from event_date) = 2022
)
SELECT
month,
COUNT(DISTINCT user_id) as mau
FROM
cte
WHERE
month = 7
and month = prev_month + 1
GROUP BY 1;

[gauravsharma]

nice use case of intersect ...thanx for the help

[nagulmeerashaik-brtw]

with cte as(
SELECT
user_id,
EXTRACT(month from event_date) as month,
lag(EXTRACT(month from event_date)) over (PARTITION BY user_id order by event_date) as prev_month
FROM
user_actions
WHERE
EXTRACT(year from event_date) = 2022
)
select month,
count(user_id) as monthly_active_users
from cte
where prev_month=6 and month=7
group by month

[harshitbhadiyadra]

Please all the solutions with explanation of Easy, Medium and Hard sections of Datalemur....

[ujjwalvarshney]

select x.* from(
SELECT date_part('Month', b.event_date) as month, count(distinct a.user_id) as monthly_active_users
FROM user_actions a join
user_actions b on a.user_id = b.user_id
where date_part('Month', a.event_date) =date_part('Month', b.event_date) -1
group by 1) as x
where x.month <>6

[harshitbhadiyadra]

can you please upload all hard problems solutions please, , its great help.

[yuvasridora]

But month is not in input table can we use 6 as month please clarify my doubt

[pitou]

beautifully summarized, i tried solving it all by myself and succeeded but the code looks ugly