9 programs
Choose the best training program according to your career.
Improve the performance of your business with our innovative Industrial Training Program.
Read More →
Our instructor-led training classes are designed to make you an expert.
Read More →
Real-life case studies are a great way to demonstrate your practical knowledge.
Read More →
Explore blogs, latest technologies and industry updates with practical examples.
Read More →
Browse all available training programs and choose the right one for your career.
Read More →Know more about who we are and what we stand for.
Discover our mission to equip individuals with essential IT skills and industry expertise.
Read More →Learn, practice and plan your career — bilkul free.
Book a free video counselling session and pick the right course for you.
Read More →Download the SQL cohort analysis guide PDF from Uncodemy: define cohorts, build retention tables and read the results, with tested queries.
This page offers Uncodemy's SQL cohort analysis guide as a PDF download. A cohort is a group of customers who share a starting point, such as the month of their first order. Cohort analysis tracks how each group behaves over time, which shows retention more honestly than a single overall average. The guide builds the full SQL step by step.
Download the guide below. Each download is its own file, and everything in it is also explained on this page.
Key terms, the five steps of a cohort query, the full retention table in SQL, how to read the retention triangle, patterns to interpret, cohort variations and common mistakes, with a practice task.
A short form asks for your details before the download starts. Your details are used only to share study material and course information.
An overall retention or repeat-purchase figure can hide what is really happening. If a business is growing quickly, most customers are new and the overall average looks healthy even if each new group is leaving faster than the last. Cohort analysis separates customers by when they started and follows each group on its own timeline, so you can ask whether customers acquired in March behave differently from those acquired in June, and whether a change such as a new onboarding flow actually improved retention. It is a standard tool in subscription, e-commerce and app analytics.
The examples use these tables: customers(customer_id, name, city, signup_date), orders(order_id, customer_id, order_date, status, order_total), order_items(order_id, product_id, quantity, unit_price) and products(product_id, product_name, category). Dates are text in YYYY-MM-DD form, so the examples run in SQLite and need small changes in other databases.
| Term | Meaning |
|---|---|
| Cohort | A group of customers who started in the same period, such as the month of first order |
| Cohort month | The month in which the customer first purchased |
| Period number | Months since the cohort month (0 for the first month, 1 for the next, and so on) |
| Retention rate | Share of the cohort that was active in a given period |
| Active customer | A customer with at least one qualifying order in the period |
Each customer's first order month and every month in which they ordered.
WITH first_order AS (
SELECT customer_id,
MIN(strftime('%Y-%m', order_date)) AS cohort_month
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
),
activity AS (
SELECT DISTINCT customer_id,
strftime('%Y-%m', order_date) AS active_month
FROM orders
WHERE status <> 'cancelled'
)
SELECT f.cohort_month, a.active_month, COUNT(*) AS customers
FROM first_order f
JOIN activity a ON a.customer_id = f.customer_id
GROUP BY f.cohort_month, a.active_month
ORDER BY f.cohort_month, a.active_month;Adds the period number and the retention rate. The month difference is computed from the year and month parts, which works in SQLite; other databases have date-difference functions.
WITH first_order AS (
SELECT customer_id, MIN(strftime('%Y-%m', order_date)) AS cohort_month
FROM orders WHERE status <> 'cancelled'
GROUP BY customer_id
),
activity AS (
SELECT DISTINCT customer_id, strftime('%Y-%m', order_date) AS active_month
FROM orders WHERE status <> 'cancelled'
),
joined AS (
SELECT f.cohort_month, a.customer_id,
(CAST(SUBSTR(a.active_month, 1, 4) AS INTEGER) * 12 + CAST(SUBSTR(a.active_month, 6, 2) AS INTEGER))
- (CAST(SUBSTR(f.cohort_month, 1, 4) AS INTEGER) * 12 + CAST(SUBSTR(f.cohort_month, 6, 2) AS INTEGER)) AS period
FROM first_order f
JOIN activity a ON a.customer_id = f.customer_id
),
cohort_size AS (
SELECT cohort_month, COUNT(*) AS size FROM first_order GROUP BY cohort_month
)
SELECT j.cohort_month, j.period,
COUNT(DISTINCT j.customer_id) AS active_customers,
ROUND(COUNT(DISTINCT j.customer_id) * 100.0 / s.size, 1) AS retention_pct
FROM joined j
JOIN cohort_size s ON s.cohort_month = j.cohort_month
GROUP BY j.cohort_month, j.period, s.size
ORDER BY j.cohort_month, j.period;Arrange the output as a triangle: cohorts down the side, periods across the top and retention percentages in the cells. Period zero is always one hundred percent because every customer in the cohort ordered in their first month.
Read across a row to see how one cohort fades; read down a column to compare cohorts at the same age. A column that improves for later cohorts suggests that something you changed helped. A sudden drop in one calendar month across all cohorts points to an external event, such as a stock-out or an outage, rather than to customer behaviour.
| Pattern in the table | Possible meaning | Next question |
|---|---|---|
| Steep fall after period 1 | Many one-time buyers | What happens in the first purchase experience? |
| Flat tail after period 3 | A loyal core remains | Who are they and how do we find more? |
| Newer cohorts retain better | Improvement in product or onboarding | Which change coincided with it? |
| Newer cohorts retain worse | Lower-quality acquisition or product issue | Which channel or offer changed? |
| Same dip in one calendar month | External event | Check operations and marketing records |
Build the table on any orders dataset. Then change the definition of active, for example to require at least two orders in the month, and see how retention changes. Write a short summary with the key pattern and one recommended action. Being able to explain a retention triangle clearly is a strong interview skill.
Want to build retention analysis the way analysts do at work? Explore Uncodemy's Data Analytics course and learn from industry trainers.