Lesson 17.5
SQL Playground Mission: Retention Cohort Report
Build a cohort table from first sessions and measure whether users return within 30 days.
Retention asks whether people come back after their first visit. In SQL, that means two steps: find each user's first session, then look for later sessions inside a time window.
This mission uses signed-in sessions only because anonymous sessions have no stable user id. The output is one row per first-session month, with a 30-day retention count and rate.
Pattern
with first_touch as (
select user_id, min(session_date) as first_seen
from sessions
where user_id is not null
group by user_id
)
select strftime(first_seen, '%Y-%m') as cohort_month,
count(*) as cohort_users
from first_touch
group by cohort_month
order by cohort_monthSchema · Website AnalyticsTable · sessions7 columns · 30 rows
Table · sessions
7 columns · 30 rowsOne row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.
session_id intuser_id intsession_date datechannel textcampaign_id intdevice textduration_seconds int
Your task
Return cohort_month, cohort_users,retained_30d, and retention_rate for signed-in users. A retained user has another session after their first session and within 30 days. Sort by cohort_month.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: cohort_month, cohort_users, retained_30d, retention_rate.
- Rows: one row per first-session month for signed-in users.
- The 2024-06 cohort has 4 users and 1 retained user, for 25.0%.