Capstone Project 2

Project Overview

You are a Data Analyst for a SaaS company. You need to generate metrics for the executive dashboard.

1. Daily Active Users (DAU)

SELECT LoginDate, COUNT(DISTINCT UserID) as DAU
FROM UserLogins
GROUP BY LoginDate;

2. Customer Churn

Find users who were active last month but not this month.

SELECT DISTINCT UserID
FROM UserLogins
WHERE LoginDate BETWEEN '2023-01-01' AND '2023-01-31'
EXCEPT
SELECT DISTINCT UserID
FROM UserLogins
WHERE LoginDate BETWEEN '2023-02-01' AND '2023-02-28';

3. Cohort Analysis (Retention)

Percentage of users from Month 1 who returned in Month 2.

WITH Cohort AS (
    SELECT UserID, MIN(MONTH(JoinDate)) as SpecificMonth
    FROM Users
    GROUP BY UserID
)
SELECT SpecificMonth, COUNT(DISTINCT l.UserID) as ReturningUsers
FROM Cohort c
JOIN UserLogins l ON c.UserID = l.UserID
WHERE MONTH(l.LoginDate) = c.SpecificMonth + 1
GROUP BY SpecificMonth;