SQL
Project
Presented by Saurav Raj
The project objective is
01. to derive actionable
insights from user and
login data.
Project
objective
and goal The goal is to provide
valuable insights into
02. user engagement,
activity patterns, and
overall usage trends
over time.
User Table
Select * from Users;
Logins Table
Select * from logins;
Query 1 Management wants to see all
the users that did not login in
the past five months
with cte1 as
(Select a.*, b.user_name
from logins a
join users b
on a.user_id = b.user_id)
Select user_id, user_name
from cte1
group by user_id, user_name
having max(login_timestamp) <
dateadd(month, -5, '2024-06-28');
Query 2 How many users and how many
sessions were at each quarter ?
Select DATEPART(qq, login_timestamp)
as quarter,
count(distinct user_id) as total_users,
count(session_id) as total_sessions
from logins
group by DATEPART(qq,
login_timestamp);
Query 3 User Id's who logged in January
2024 but not in November 2023
Select user_id
from logins
where datepart(year, login_timestamp) =
2024 and
datepart(month, login_timestamp) = 01
Except
Select user_id
from logins
where datepart(year, login_timestamp) =
2023 and
datepart(month, login_timestamp) = 11;
What is the percentage change
Query 4 in sessions from the last
quarter?
with cte1 as
(Select datetrunc(quarter, min(login_timestamp)) as first_quarter_date,
count(distinct user_id) as user_count,
count(*) as session_cnt
from logins
group by datepart(quarter, login_timestamp))
Select *,
lag(session_cnt, 1)over(order by first_quarter_date) as session_prev_cnt,
coalesce(round((session_cnt-(lag(session_cnt, 1)over(order by
first_quarter_date)))*100.0/
lag(session_cnt, 1)over(order by first_quarter_date), 2), 0) as
session_percent_change
from cte1;
Query 5 Which user had the highest
session score(max) for each
day?
with cte1 as
(Select a.user_id, b.user_name, cast(a.login_timestamp as date) as login_date,
sum(a.session_score) as score
from logins a
join users b
on a.user_id = b.user_id
group by a.user_id, b.user_name, cast(a.login_timestamp as date))
Select user_id, user_name, login_date, score
from
(Select *,
row_number()over(partition by login_date order by score desc) as rn
from cte1) a
where rn = 1;
Query 6 Which user have had a session
every single day since their
first login?
with cte1 as
(Select user_id, count(user_id) as login_count_per_user,
min(cast(login_timestamp as date)) as first_login_date ,
datediff(day, min(cast(login_timestamp as date)),
max(cast(login_timestamp as date))) + 1 as
no_of_days_login_required
from logins
group by user_id)
Select * from cte1
where login_count_per_user = no_of_days_login_required;
Query 7 On what dates there were no
logins at all ?
With cte1 as
(select min(cast(login_timestamp as date)) as first_date, max(cast
(login_timestamp as date)) as last_date
from logins
union all
select dateadd(day, 1, first_date) as first_date, last_date
from cte1
where first_date < last_date)
Select first_date from cte1
where first_date not in (select distinct cast(login_timestamp as date)
from logins)
option(maxrecursion 500);
Thank you
very much!