Cohort Analysis Explained with SQL — Retention Made Simple!

Описание: Want to master Cohort Analysis using SQL? In this video, I walk you through how to group users by signup month and analyze their retention behavior over time using clean, simple SQL queries. Whether you're a data analyst, product manager, or someone preparing for a data interview, this one's for you!

We’ll cover:
✅ What is cohort analysis?
✅ How to build it from scratch using SQL
✅ How to calculate monthly retention
✅ Best practices for real-world analysis

🧠 What You’ll Learn:
How to create a users and events table
How to group users into cohorts
How to calculate retention using TIMESTAMPDIFF in MySQL
How to read cohort tables and make business decisions

Practice This Yourself – SQL Script

-- Users table: stores signup info
CREATE TABLE users (
user_id INT PRIMARY KEY,
signup_date DATE
);

-- Events table: stores user activity info
CREATE TABLE user_events (
event_id INT PRIMARY KEY,
user_id INT,
event_date DATE,
event_type VARCHAR(50),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Insert users
INSERT INTO users (user_id, signup_date) VALUES
(1, '2024-01-15'),
(2, '2024-01-20'),
(3, '2024-02-05'),
(4, '2024-02-10'),
(5, '2024-03-02'),
(6, '2024-03-25');

-- Insert events
INSERT INTO user_events (event_id, user_id, event_date, event_type) VALUES
(101, 1, '2024-01-16', 'login'),
(102, 1, '2024-02-10', 'login'),
(103, 1, '2024-03-15', 'purchase'),

(104, 2, '2024-01-21', 'login'),
(105, 2, '2024-02-18', 'purchase'),

(106, 3, '2024-02-06', 'login'),
(107, 3, '2024-03-10', 'login'),

(108, 4, '2024-02-12', 'login'),

(109, 5, '2024-03-03', 'login'),
(110, 5, '2024-04-01', 'purchase'),

(111, 6, '2024-03-26', 'login'),
(112, 6, '2024-04-05', 'login');

#sql #statistics #datascience #sqltutorial #dataanalytics #cohort #marketinganalytics