DB Exercises
0
← Exercises/
Business Analytics•2 of 14
PrevNext
Business AnalyticsAdvanced

Monthly User Cohort Retention Matrix

Scenario

The holy grail of product-market fit is Cohort Retention: tracking whether users who signed up in month M come back and remain active in month M+1, M+2, etc.

Task
Build a cohort retention summary tracking the **signup cohort month** and the **activity month**. Write a query that computes: 1. `cohort_month`: Truncate user `signup_date` to month (`DATE_TRUNC('month', u.signup_date)::date`) 2. `activity_month`: Truncate event `event_date` to month (`DATE_TRUNC('month', e.event_date)::date`) 3. `active_users`: Count of distinct `user_id`s active in that month Join `cohort_users` to `user_events` on `user_id`. Group by `cohort_month` and `activity_month`. Order by `cohort_month` ascending, then by `activity_month` ascending.
Expected Output Format

Cohort table displaying cohort_month, activity_month, and active_users.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Monthly User Cohort Retention Matrix” SQL Problem

Business Scenario

The holy grail of product-market fit is Cohort Retention: tracking whether users who signed up in month M come back and remain active in month M+1, M+2, etc.

Expected Output & Criteria

Cohort table displaying cohort_month, activity_month, and active_users.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE cohort_users (
        user_id INT PRIMARY KEY,
        signup_date DATE NOT NULL
      );

      CREATE TABLE user_events (
        event_id INT PRIMARY KEY,
        user_id INT REFERENCES cohort_users(user_id),
        event_date DATE NOT NULL
      );

Target SQL Clauses & Operators

DATE_TRUNCGROUP BY

Want more Business Analytics practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Business Analytics Track→