New & Returning Users | SQL Interview Question

Published: 06 February 2024
on channel: Absent Data

2,233

113

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;

On this page of the site you can watch the video online New & Returning Users | SQL Interview Question with a duration of hours minute second in good quality, which was uploaded by the user Absent Data 06 February 2024, share the link with friends and acquaintances, this video has already been watched 2,233 times on youtube and it was liked by 113 viewers. Enjoy your viewing!

264,230

6.9K