A Practical Guide to Tracking User Behavior Over Time With Nothing but SQL
Every business wants to understand how its users behave over time. Are they coming back? Are they spending more? Are they disappearing after the first month? These questions sit at the heart of growth strategy, yet many analysts reach for expensive tools or complex pipelines to answer them. The truth is, one of the most powerful techniques for understanding user retention lives inside the SQL you already know.
Cohort analysis groups users by a shared characteristic, most commonly their sign-up date, and then tracks their behavior in subsequent time periods. In this article, we will build a complete cohort retention table from scratch using nothing but pure SQL. No Python, no R, no BI tools. Just SELECT statements, common table expressions, and a handful of date functions. By the end, you will have a reusable query pattern that works on virtually any transactional dataset.
What Is Cohort Analysis and Why Does It Matter?
Cohort analysis is a technique that divides users into groups, called cohorts, based on a shared starting event. The most common starting event is the month a user first signed up or made their first purchase. Once you have defined your cohorts, you track how each group behaves in the months that follow. For example, of all users who signed up in January 2024, how many placed an order in February? How many were still active in June?
This matters because aggregate metrics like total monthly active users can hide critical trends. Your overall numbers might look flat, but that could mean you are acquiring new users at the same rate you are losing old ones. Cohort analysis reveals the underlying pattern. It tells you whether your January users retained better than your March users, whether a product change improved long-term engagement, or whether a specific acquisition channel brings users who stick around. It is, in many ways, the single most important retention analysis you can run.
Setting Up Our Example Data
Throughout this article, we will work with two simple tables that you would find in almost any e-commerce or SaaS database. The first is a users table that records when each user signed up. The second is an orders table that records each transaction a user makes. Here is what a sample of our users table looks like:
-- users table sample
-- user_id | created_at
-- --------|------------
-- 1 | 2024-01-05
-- 2 | 2024-01-12
-- 3 | 2024-01-22
-- 4 | 2024-02-03
-- 5 | 2024-02-14
-- 6 | 2024-03-01
-- 7 | 2024-03-18
SELECT user_id, created_at
FROM users
ORDER BY created_at
LIMIT 7;
Our users table has seven users spread across three months: three signed up in January 2024, two in February, and two in March. Each user has a unique user_id and a created_at timestamp. Now here is our orders table:
-- orders table sample
-- order_id | user_id | order_date | amount
-- ---------|---------|------------|-------
-- 101 | 1 | 2024-01-15 | 49.99
-- 102 | 2 | 2024-01-20 | 29.99
-- 103 | 1 | 2024-02-10 | 59.99
-- 104 | 3 | 2024-02-18 | 19.99
-- 105 | 4 | 2024-02-25 | 39.99
-- 106 | 1 | 2024-03-05 | 24.99
-- 107 | 2 | 2024-03-12 | 44.99
-- 108 | 5 | 2024-03-15 | 34.99
-- 109 | 6 | 2024-03-22 | 54.99
-- 110 | 4 | 2024-04-01 | 29.99
-- 111 | 1 | 2024-04-10 | 39.99
-- 112 | 6 | 2024-04-18 | 19.99
SELECT order_id, user_id, order_date, amount
FROM orders
ORDER BY order_date
LIMIT 12;
The orders table shows twelve transactions from our seven users. Notice that user 1, who signed up in January, has orders in January, February, March, and April, making them a highly retained user. User 3, also from the January cohort, only ordered once in February and then disappeared. This kind of variation is exactly what cohort analysis is designed to reveal.
Step 1: Assign Each User to a Cohort
The first step is to determine which cohort each user belongs to. We do this by truncating the user’s sign-up date to the month level using DATE_TRUNC. This converts any date within a month to the first day of that month, giving us a clean cohort label.
SELECT
user_id,
created_at,
DATE_TRUNC('month', created_at) AS cohort_month
FROM users
ORDER BY created_at;
This query produces a result where user 1 (created_at 2024–01–05) gets a cohort_month of 2024–01–01, user 4 (created_at 2024–02–03) gets 2024–02–01, and user 6 (created_at 2024–03–01) gets 2024–03–01. The DATE_TRUNC function strips away the day component and normalizes every sign-up date to the first of its month. Now every user carries a cohort label that we can use for grouping.
Step 2: Calculate the Activity Month for Each Order
Next, we need to figure out how many months after sign-up each order occurred. We do this by joining the orders table to the users table, computing the cohort month for each user, and then calculating the difference in months between the order date and the cohort month. This difference gives us the month_number, where 0 means the user ordered in the same month they signed up, 1 means one month later, and so on.
SELECT
o.user_id,
DATE_TRUNC('month', u.created_at) AS cohort_month,
DATE_TRUNC('month', o.order_date) AS order_month,
EXTRACT(YEAR FROM AGE(
DATE_TRUNC('month', o.order_date),
DATE_TRUNC('month', u.created_at)
)) * 12
+ EXTRACT(MONTH FROM AGE(
DATE_TRUNC('month', o.order_date),
DATE_TRUNC('month', u.created_at)
)) AS month_number
FROM orders o
JOIN users u ON o.user_id = u.user_id
ORDER BY cohort_month, month_number;
Looking at the results, user 1 from the 2024–01–01 cohort has orders at month_number 0 (January order), month_number 1 (February order), month_number 2 (March order), and month_number 3 (April order). User 3, also in the January cohort, only appears at month_number 1 because their single order was in February. User 4 from the 2024–02–01 cohort has a month_number 0 order in February and a month_number 2 order in April. This month_number column is the key to building our retention grid.
Step 3: Count Distinct Users per Cohort and Month
Now we wrap the previous logic in a CTE and aggregate the results. We want to know, for each cohort and each month_number, how many distinct users placed at least one order. Using COUNT(DISTINCT user_id) ensures that a user who placed three orders in the same month is only counted once.
WITH user_activity AS (
SELECT
o.user_id,
DATE_TRUNC('month', u.created_at) AS cohort_month,
EXTRACT(YEAR FROM AGE(
DATE_TRUNC('month', o.order_date),
DATE_TRUNC('month', u.created_at)
)) * 12
+ EXTRACT(MONTH FROM AGE(
DATE_TRUNC('month', o.order_date),
DATE_TRUNC('month', u.created_at)
)) AS month_number
FROM orders o
JOIN users u ON o.user_id = u.user_id
)
SELECT
cohort_month,
month_number,
COUNT(DISTINCT user_id) AS num_users
FROM user_activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;
The result now shows aggregated counts. For the 2024–01–01 cohort, we see 2 users active at month_number 0 (users 1 and 2 both ordered in January), 2 users at month_number 1 (users 1 and 3 ordered in February), 2 users at month_number 2 (users 1 and 2 ordered in March), and 1 user at month_number 3 (only user 1 ordered in April). For the 2024–02–01 cohort, we see 2 users at month_number 0 and 1 user at month_number 2. For the 2024–03–01 cohort, we see 2 users at month_number 0 and 1 user at month_number 1. These counts form the raw data for our retention table.
Step 4: Calculate the Cohort Size
To calculate retention percentages, we need to know how many users were in each cohort to begin with. We calculate the cohort size by counting the total number of users who signed up in each cohort month. This gives us the denominator for our retention percentage.
SELECT
DATE_TRUNC('month', created_at) AS cohort_month,
COUNT(user_id) AS cohort_size
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY cohort_month;
This returns three rows: the 2024–01–01 cohort has 3 users, the 2024–02–01 cohort has 2 users, and the 2024–03–01 cohort has 2 users. These are the total number of sign-ups per month, regardless of whether they ever placed an order. We will use these numbers as the baseline when computing retention percentages in the final step.
Step 5: Build the Complete Retention Table
Now we bring everything together. The final query uses three CTEs: one to compute user activity with cohort assignments and month numbers, one to count distinct users per cohort and month, and one to calculate cohort sizes. We then join these together and compute the retention percentage by dividing the number of active users by the cohort size and multiplying by 100.
WITH user_activity AS (
SELECT
o.user_id,
DATE_TRUNC('month', u.created_at) AS cohort_month,
EXTRACT(YEAR FROM AGE(
DATE_TRUNC('month', o.order_date),
DATE_TRUNC('month', u.created_at)
)) * 12
+ EXTRACT(MONTH FROM AGE(
DATE_TRUNC('month', o.order_date),
DATE_TRUNC('month', u.created_at)
)) AS month_number
FROM orders o
JOIN users u ON o.user_id = u.user_id
),
cohort_activity AS (
SELECT
cohort_month,
month_number,
COUNT(DISTINCT user_id) AS num_users
FROM user_activity
GROUP BY cohort_month, month_number
),
cohort_size AS (
SELECT
DATE_TRUNC('month', created_at) AS cohort_month,
COUNT(user_id) AS num_users_total
FROM users
GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
cs.cohort_month,
cs.num_users_total AS cohort_size,
ca.month_number,
ca.num_users,
ROUND(
100.0 * ca.num_users / cs.num_users_total, 1
) AS retention_pct
FROM cohort_activity ca
JOIN cohort_size cs ON ca.cohort_month = cs.cohort_month
ORDER BY cs.cohort_month, ca.month_number;
The final result is a complete retention table. For the January 2024 cohort (3 users), we see: month 0 has 2 active users (66.7% retention), month 1 has 2 active users (66.7%), month 2 has 2 active users (66.7%), and month 3 has 1 active user (33.3%). For the February 2024 cohort (2 users), month 0 has 2 active users (100%), and month 2 has 1 active user (50%). For the March 2024 cohort (2 users), month 0 has 2 active users (100%) and month 1 has 1 active user (50%). Notice that the January cohort never reaches 100% at month 0 because user 3, while signed up in January, did not place their first order until February. This is an important distinction: we are measuring ordering activity, not sign-up activity.
Reading and Interpreting the Retention Table
When reading a retention table, each row represents a single cohort. Scan across a row to see how that cohort’s engagement changes over time. In our example, the January cohort starts at 66.7% in month 0 and holds steady through month 2 before dropping to 33.3% in month 3. This flat retention curve in the early months is a positive signal, suggesting that the users who activate early tend to stick around. A row that drops sharply from month 0 to month 1, on the other hand, signals an onboarding or activation problem.
Reading down a column tells you how a particular month_number compares across cohorts. For example, looking at month_number 0 across all cohorts, we see 66.7% for January, 100% for February, and 100% for March. This tells us that the February and March cohorts were more active in their sign-up month than the January cohort. If you see a consistent improvement in column values over time, it might indicate that product improvements or marketing changes are having a positive effect on early user behavior.
Finally, look for anomalies. In our data, the February cohort has no activity at month_number 1 but reappears at month_number 2. This gap could indicate a seasonal effect, a billing cycle, or simply a small sample size. In real-world datasets with hundreds or thousands of users, these anomalies become more meaningful. A sudden spike in retention at a particular month across multiple cohorts might point to a product event like a new feature launch or a promotional campaign that re-engaged dormant users.
Adapting the Query for Different SQL Dialects
The queries in this article use PostgreSQL syntax, but adapting them for other databases is straightforward.
In MySQL, replace DATE_TRUNC(‘month’, date_column) with DATE_FORMAT(date_column, ‘%Y-%m-01’) and use TIMESTAMPDIFF(MONTH, start_date, end_date) instead of the EXTRACT and AGE combination for calculating the month difference.
In SQL Server, use FORMAT(date_column, ‘yyyy-MM-01’) or DATEADD(MONTH, DATEDIFF(MONTH, 0, date_column), 0) for month truncation and DATEDIFF(MONTH, start_date, end_date) for the month difference calculation.
BigQuery users can use DATE_TRUNC(date_column, MONTH) and DATE_DIFF(end_date, start_date, MONTH). The CTE structure, COUNT(DISTINCT), ROUND, and JOIN logic remain identical across all dialects.
In this article, we built a complete cohort retention analysis using nothing but SQL. We started by assigning users to cohorts based on their sign-up month using DATE_TRUNC. We then calculated the month_number for each order by computing the difference between the order month and the cohort month. We aggregated these into counts using COUNT(DISTINCT user_id), computed cohort sizes, and finally joined everything together to produce retention percentages with ROUND. The entire analysis relies on five fundamental SQL concepts: CTEs, JOINs, GROUP BY, date functions, and aggregate functions.
The beauty of this approach is its portability and transparency. Because it is pure SQL, you can run it on any database, embed it in any reporting tool that supports SQL queries, and share it with colleagues who may not know Python or R. The query is also easy to extend. You could add a WHERE clause to filter by user segment, change the time granularity from months to weeks, or add revenue metrics alongside user counts. You could even pivot the results into a matrix format using CASE WHEN statements if your database supports them.
If you found this useful, try running the final query against your own data. Replace the table names and column names, adjust the DATE_TRUNC granularity to match your use case, and see what your retention curves look like. Cohort analysis is one of those techniques that becomes more valuable the more you use it, and now you have the SQL to do it from scratch every time.
Cohort Analysis Using Pure SQL was originally published in Data Decoded on Medium, where people are continuing the conversation by highlighting and responding to this story.