---
title: Calculate Retention in SQL: Classic, Rolling, Cumulative
description: Copy-ready SQL and spreadsheet formulas for classic, rolling and cumulative retention, plus four checks that tell you which definition a tool really uses.
url: https://retentioncohorts.com/compute/
---

[Home](https://retentioncohorts.com/) / Compute

# Compute classic, rolling and cumulative retention

A copy-ready SQL query, spreadsheet formulas and four checks that catch the wrong definition.

**Short answer**

All three come from one table of user and active date. Classic counts users active where day_n = N, rolling where day_n >= N, cumulative where day_n is between 1 and N. Divide by installs, include only cohorts that have reached Day N, and record the as-of date for rolling.

## How do you calculate retention in SQL?

Every definition starts from the same raw data: which days each user opened the app after installing. Here a return is any app open; to use a different return event, such as a purchase, filter the activity table to that event first.

```
-- installs(user_id, install_date, channel)
-- activity(user_id, active_date)   one row per user per day they opened the app
WITH returns AS (
  SELECT DISTINCT a.user_id, (a.active_date - i.install_date) AS day_n
  FROM activity a
  JOIN installs i ON i.user_id = a.user_id
  WHERE a.active_date > i.install_date          -- Day 0 never counts
)
SELECT
  i.install_date,
  COUNT(DISTINCT i.user_id)                                              AS installs,
  COUNT(DISTINCT CASE WHEN r.day_n = 7 THEN r.user_id END)              AS classic_d7,
  COUNT(DISTINCT CASE WHEN r.day_n >= 7 THEN r.user_id END)             AS rolling_d7,
  COUNT(DISTINCT CASE WHEN r.day_n BETWEEN 1 AND 7 THEN r.user_id END)  AS cumulative_d7
FROM installs i
LEFT JOIN returns r ON r.user_id = i.user_id
WHERE i.install_date + 7 <= (SELECT MAX(active_date) FROM activity)     -- only cohorts that reached Day 7
GROUP BY i.install_date
ORDER BY i.install_date;
```

Change the 7 to any Day N. For a full cohort table, compute day_n once and pivot, or repeat the three columns for each day you need. Add `i.channel` to the SELECT and GROUP BY to split by channel.

## In a spreadsheet

```
Layout: one row per user. Column A = install date, B = channel,
C to Q = Day 0 to Day 14 (1 if the user opened the app that day, else 0).
Day 1 is column D, Day 7 is column J, Day 14 is column Q.

Classic Day 7      =J2
Rolling Day 7      =IF(COUNTIF(J2:Q2,1)>0,1,0)     Day 7 to the last day you have
Cumulative Day 7   =IF(COUNTIF(D2:J2,1)>0,1,0)     Day 1 to Day 7

Retention for a cohort = AVERAGE of a helper column over that cohort's rows
(for example with AVERAGEIFS on the install date).
```

The [ten-users view](https://retentioncohorts.com/#ten-users) is exactly this layout with fourteen days.

## Four checks that catch a wrong definition

1. **Classic is the floor:** no rolling or cumulative cell may be below the classic cell for the same cohort and day.
2. **Cumulative never falls** across a row.
3. **Rolling never rises** across a row, for one as-of date.
4. **Identities:** on a cohort's last observed day, rolling equals classic; and rolling Day 1 equals cumulative on the last observed day. In the sample app, the 1 Sep cohort's rolling Day 1 and cumulative Day 14 are both 108 of 148 users.

If a tool's columns break one of these, it is using a different definition or a different Day 0 than its label suggests.

See this on your own app

If your installs and in-app events are already in Linkrunner, you can export them rather than build the installs and activity tables by hand, then run the query above on the export.

- [Data export in the Linkrunner docs](https://docs.linkrunner.io/features/data-export?utm_source=retentioncohorts&utm_medium=referral&utm_campaign=compute&utm_content=panel)
- [The events tab in the Linkrunner docs](https://docs.linkrunner.io/features/events-tab?utm_source=retentioncohorts&utm_medium=referral&utm_campaign=compute&utm_content=panel)
- [Create a free account (25k installs free)](https://dashboard.linkrunner.io/sign-in?utm_source=retentioncohorts&utm_medium=referral&utm_campaign=compute&utm_content=panel)

## Questions

**How do I calculate classic retention in SQL?**

Count distinct users whose activity falls on exactly install date plus N days, divide by the installs in the cohort, and only include cohorts that have reached Day N. The query on this page does that for Day 7.

**Should Day 0 activity count as a return?**

No. Day 0 is the install day, and almost every user is active on it, so counting it would inflate Day 1 cumulative and rolling numbers. Filter it out before counting.

**Calendar days or 24-hour windows?**

Either works if you are consistent and say which. Calendar days in one fixed time zone are easier to reconcile with dashboards; 24-hour windows treat late-night installs more fairly.

**What is the difference between classic, rolling and cumulative retention?**

Classic counts users active on exactly Day N. Rolling counts users active on Day N or any later day, and cumulative counts users active at least once from Day 1 to Day N. They use the same raw activity but answer different questions.

**Why is my rolling retention higher than classic?**

Rolling also counts users who skipped Day N but came back later, so it can never be lower than classic. The gap grows the longer a cohort has been observed, because late returns keep adding to it.

**What is cumulative retention?**

Cumulative retention is the share of an install cohort that opened the app at least once between Day 1 and Day N. It never falls as N grows and it is fixed once Day N has passed, which makes it a good activation measure.