Retention in SQL: distinguish active users from returning users

An active user meets a defined activity condition during a period. A returning user is active during that period and has qualifying history before it. Cohort retention asks how many members of a fixed earlier group are active again. These measures have different denominators and should not share a vague “retention” label.

For this lesson, activity means at least one purchase in a calendar month. A product team might instead choose a meaningful in-app action, but a login or page view should not be assumed to represent value without discussion.

We use the advanced SQL lab, with five synthetic customers and a complete exercise window through April 2026.

Define new and returning within the observed history

A customer's first observed purchase month is their new-customer month. If they purchase in a later month, they count as returning in that month. Multiple purchases during the first month do not make them a returning customer under this monthly definition.

WITH firsts AS (
    SELECT customer_id,
           SUBSTR(MIN(purchased_at), 1, 7) AS first_month
    FROM purchases
    GROUP BY customer_id
), monthly AS (
    SELECT DISTINCT customer_id,
           SUBSTR(purchased_at, 1, 7) AS activity_month
    FROM purchases
)
SELECT m.activity_month,
       COUNT(*) AS active_customers,
       SUM(CASE WHEN m.activity_month = f.first_month THEN 1 ELSE 0 END)
           AS new_customers,
       SUM(CASE WHEN m.activity_month > f.first_month THEN 1 ELSE 0 END)
           AS returning_customers
FROM monthly AS m
JOIN firsts AS f ON f.customer_id = m.customer_id
GROUP BY m.activity_month
ORDER BY m.activity_month;

Expected output:

Each row reconciles: new plus returning equals active. That identity holds because every active customer has a first observed month at or before the activity month and the two categories are mutually exclusive.

A repeat order is not always a returning-month customer

B purchases on January 7 and January 25. B is a repeat purchaser within January but remains a new customer in the monthly acquisition classification. In March, B is returning because its first observed month is January.

Neither definition is wrong. They answer different questions. If the business wants “made at least a second purchase,” use purchase sequence and a stated period rather than reusing the monthly returning flag.

The LAG/LEAD lesson shows how to inspect purchase sequences while preserving prior history.

Returning-user share is not cohort retention

In April, two of the three active customers are returning, so returning share is 66.7%. That tells you the composition of April's active population.

January-cohort activity in April is one of two original members, or 50%. That tells you what happened to a fixed acquisition group. April's new customer E belongs in the active-user denominator but not in January's cohort denominator.

A business can acquire many new users and see returning share fall even while each cohort's retention improves. Conversely, returning share can rise because acquisition collapsed. The measure needs to be interpreted alongside acquisition and cohort behaviour.

For a complete implementation of the fixed-denominator view, see monthly cohort SQL.

Define consecutive activity and reactivation separately

A returns in February, is inactive in March and purchases again in April. Under our definition, A is returning in April. If your dashboard calls that “retained from March,” it would be misleading because A was not active in March.

You may want three returning categories: active in both current and previous periods, reactivated after a gap, and previously active but now inactive. Define the reference window for each. “Returning after any prior activity” and “retained from last month” are not interchangeable.

Also decide how much inactivity constitutes a meaningful gap. Calendar months can be appropriate for one product and too coarse for another. The choice should follow the usage pattern, not a default chart template.

Validate identity and observation coverage

User IDs can change through account merges, device resets or guest checkout. If identity is unstable, both new and returning counts can be distorted. Document the identity resolution policy and avoid treating a newly seen technical ID as proof of a new person.

Incomplete history also makes established customers look new. Use “first observed” when the source starts at a limited date. A data extract that begins in January cannot establish that no customer bought in December.

Check that event eligibility is consistent across periods. If the activity definition changes from purchase to page view, the resulting series is not directly comparable without restatement or clear versioning.

SQL DISTINCT and grouped aggregates implement the counting mechanics; they do not determine the right business event. SQLite SELECT reference.

Exercise: calculate April's customers who were also active in March. D qualifies, while A is reactivated and E is new. Explain why the result differs from the two returning customers in the table.

NeuraPath's Data Analytics with Generative AI course covers SQL, business metrics and interpretation. A useful retention report gives each of these populations its own definition, so a polished dashboard or AI summary does not blur different customer behaviours into one number.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build a monthly customer cohort table in SQL.
• Continue with SQL gaps and islands for consecutive activity days.