New & Returning Users | SQL Interview Question
You will be tasked with finding the new and returning daily users based on a simple dataset of datetime and users. You can see how the query is built below. #sqlinterview
with first_visit_cte as (
select
user_id,
min(datetime) as first_visit
from
user_activity
group by
user_id
)
select
count(case when a.datetime = b.first_visit then "New" end) as New_Users,
count(case when a.datetime != b.first_visit then "Return" end) as Returning_Users
from
user_activity a
join
first_visit_cte b
on
a.user_id = b.user_id;
Watch video New & Returning Users | SQL Interview Question online without registration, duration hours minute second in high quality. This video was added by user Absent Data 06 February 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,233 once and liked it 113 people.