Retention cohort analysis with plain SQL

📰 Dev.to · Zenovay

Learn to perform retention cohort analysis using plain SQL to understand user retention patterns and improve business decisions

intermediate Published 8 Jun 2026

Action Steps

Define the cohort for each user based on their first activity week
Collect weekly activity data for each user
Write a SQL query to calculate the retention rate for each cohort
Visualize the retention data as a triangle to compare cohorts
Refine the query to account for potential mistakes and ensure accurate results

Who Needs to Know This

Data analysts and product managers can benefit from this technique to analyze user retention and identify areas for improvement

Key Insight

💡 Retention cohort analysis helps identify whether newer cohorts retain better than older ones, informing product and business strategies

Full Article

Title: Retention cohort analysis with plain SQL

URL Source: https://dev.to/zenovay/retention-cohort-analysis-with-plain-sql-356c

Published Time: 2026-06-08T18:09:38Z

Markdown Content:
[Skip to content](https://dev.to/zenovay/retention-cohort-analysis-with-plain-sql-356c#main-content)

[![Image 1: DEV Community](https://media2.dev.to/dynamic/image/quality=100/https://dev-to-uploads.s3.amazonaws.com/uploads/logos/resized_logo_UQww2soKuUsjaOGNB38o.png)](https://dev.to/)

[Powered by Algolia](https://www.algolia.com/developers/?utm_source=devto&utm_medium=referral)

[Log in](https://dev.to/enter?signup_subforem=1)[Create account](https://dev.to/enter?signup_subforem=1&state=new-user)

## DEV Community

![Image 2](https://assets.dev.to/assets/heart-plus-active-9ea3b22f2bc311281db911d416166c5f430636e76b15cd5df6b3b841d830eefa.svg)0 Add reaction

![Image 3](https://assets.dev.to/assets/sparkle-heart-5f9bee3767e18deb1bb725290cb151c25234768a0e9a2bd39370c382d02920cf.svg)0 Like ![Image 4](https://assets.dev.to/assets/multi-unicorn-b44d6f8c23cdd00964192bedc38af3e82463978aa611b4365bd33a0f1f4f3e97.svg)0 Unicorn ![Image 5](https://assets.dev.to/assets/exploding-head-daceb38d627e6ae9b730f36a1e390fca556a4289d5a41abb2c35068ad3e2c4b5.svg)0 Exploding Head ![Image 6](https://assets.dev.to/assets/raised-hands-74b2099fd66a39f2d7eed9305ee0f4553df0eb7b4f11b01b6b1b499973048fe5.svg)0 Raised Hands ![Image 7](https://assets.dev.to/assets/fire-f60e7a582391810302117f987b22a8ef04a2fe0df7e3258a5f49332df1cec71e.svg)0 Fire

0 Jump to Comments 0 Save Boost

Copy link

Copied to Clipboard

[Share to X](https://twitter.com/intent/tweet?text=%22Retention%20cohort%20analysis%20with%20plain%20SQL%22%20by%20Zenovay%20%23DEVCommunity%20https%3A%2F%2Fdev.to%2Fzenovay%2Fretention-cohort-analysis-with-plain-sql-356c)[Share to LinkedIn](https://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fdev.to%2Fzenovay%2Fretention-cohort-analysis-with-plain-sql-356c&title=Retention%20cohort%20analysis%20with%20plain%20SQL&summary=Everyone%20wants%20a%20retention%20chart%20and%20most%20reach%20for%20a%20tool.%20You%20can%20get%20the%20core%20cohort%20triangle%20from...&source=DEV%20Community)[Share to Facebook](https://www.facebook.com/sharer.php?u=https%3A%2F%2Fdev.to%2Fzenovay%2Fretention-cohort-analysis-with-plain-sql-356c)[Share to Mastodon](https://s2f.kytta.dev/?text=https%3A%2F%2Fdev.to%2Fzenovay%2Fretention-cohort-analysis-with-plain-sql-356c)

[Share Post via...](https://dev.to/zenovay/retention-cohort-analysis-with-plain-sql-356c#)[Report Abuse](https://dev.to/report-abuse)

[![Image 8: Zenovay](https://media2.dev.to/dynamic/image/width=50,height=50,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F3772909%2F8887f0bd-adb2-4162-a1ab-3e20ca842724.png)](https://dev.to/zenovay)

[Zenovay](https://dev.to/zenovay)
Posted on Jun 8

# Retention cohort analysis with plain SQL

[#sql](https://dev.to/t/sql)[#database](https://dev.to/t/database)[#analytics](https://dev.to/t/analytics)[#webdev](https://dev.to/t/webdev)

Everyone wants a retention chart and most reach for a tool. You can get the core cohort triangle from raw events with one SQL query. Here is the pattern we use, and the mistake that quietly makes it wrong.

I build this for Zenovay (web analytics) where we show retention by signup cohort. The shape below is database agnostic, written for Postgres.

## [](https://dev.to/zenovay/retention-cohort-analysis-with-plain-sql-356c#the-idea) The idea

A cohort is a group of users bucketed by when they first appeared (usually signup week). For each cohort you measure what fraction are still active N weeks later. Plot it as a triangle and you can see whether newer cohorts retain better than older ones.

## [](https://dev.to/zenovay/retention-cohort-analysis-with-plain-sql-356c#the-two-inputs) The two inputs

You need two things per user: their cohort (first activity week) and every week they w

Read full article → ← Back to Reads

Retention cohort analysis with plain SQL

Full Article

Related Videos