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_month

Schema · Website Analytics Table · sessions 7 columns · 30 rows

Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows

session_id int user_id int session_date date channel text campaign_id int device text duration_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.