SQL Churn Interview Questions: Reactivation, Pauses, and Subscription Boundaries

SQL churn interview questions are usually tests of lifecycle definitions before they are tests of aggregation. A cancellation request, a lapse in service, and absence from an ending snapshot can describe three different things. Reactivation makes the difference visible: an account can leave during August and still be active at both monthly cutoffs.

For data analyst and data scientist interviews, start by naming the entity, the definition of active, and the observation boundary. Then build one account-level result before calculating a rate. The worked example below distinguishes endpoint loss from exits, returns, and pauses; it is original practice material, not a reported employer assessment.

This article focuses on subscription state rather than inactivity-based product retention or churn prediction.

What does churn mean in this question?

Subscription churn measures the loss of subscriptions under a declared rule. Account churn measures the loss of qualifying service for an account, potentially across several subscriptions. Endpoint churn compares two snapshots; event-based churn detects transitions. These measures can disagree without either calculation being wrong.

Ask whether the output counts accounts, subscription IDs, seats, or recurring revenue. An account with two plans can cancel one plan without losing all service. A downgrade can reduce revenue without changing the customer count. Stripe's subscription churn overview discusses customer loss and revenue implications, but the interview still needs its own precise metric contract.

For our exercise, an account is active whenever at least one qualifying entitlement interval covers the cutoff. Collection pauses preserve entitlement. We measure accounts active at August 1, 2026, 00:00 UTC that are inactive at September 1, 2026, 00:00 UTC. Call this endpoint account churn, not the number of cancellations during August.

Why cancellation and pause fields are not enough

A customer can request cancellation today while retaining access until the paid period ends. Stripe explicitly supports end-of-period cancellation and allows reversal before that cancellation takes effect. Therefore, a request timestamp is not automatically the effective end of service. Stripe cancellation documentation

Similarly, Stripe's payment-collection pause leaves the subscription status unchanged. It is not equivalent to every product's concept of a paused subscription. Whether access continues, billing stops, or recurring revenue becomes zero belongs to the product's business contract. Stripe pause-payment documentation

In an interview, say which field or transformation produces the effective entitlement end. Do not infer it from an ambiguous canceled_at column name. If the schema cannot distinguish request time from effective time, identify the missing information and state a conditional assumption before writing SQL.

Use half-open subscription intervals

Represent entitlement as [starts_at, ends_at): active at the start, inactive at the end. A NULL end means no known effective end in this fixture. PostgreSQL's range documentation explains inclusive and exclusive bounds and uses lower-inclusive, upper-exclusive bounds as the default range-constructor form. PostgreSQL range types

The membership predicate is starts_at <= cutoff AND (ends_at > cutoff OR ends_at IS NULL). Avoid BETWEEN here because its inclusive upper comparison changes the end boundary. Back-to-back plans ending and starting at the same instant should not create a fictitious lapse in account coverage.

Both cutoffs are exact instants. An account ending exactly September 1 is inactive at the ending cutoff, so it contributes to endpoint loss. That does not mean its exit event belongs to the half-open August event window: [August 1, September 1) excludes that instant. Explicitly separate these two questions instead of adjusting timestamps by an arbitrary microsecond.

Load a six-account lifecycle fixture

Run these blocks in a fresh DuckDB session. The timestamps are normalized UTC instants stored without timezone conversion for this exercise. The syntax also resembles PostgreSQL, but our execution check uses DuckDB 1.5.2, not a production billing system.

CREATE TABLE entitlement (
  account_id INTEGER, plan_id VARCHAR,
  starts_at TIMESTAMP, ends_at TIMESTAMP
);
INSERT INTO entitlement VALUES
 (1,'a','2026-07-01','2026-08-10'),
 (1,'b','2026-08-20',NULL),
 (2,'a','2026-07-01','2026-08-15'),
 (3,'a','2026-07-01',NULL),
 (4,'a','2026-07-01','2026-08-05'),
 (4,'b','2026-07-20',NULL),
 (5,'a','2026-08-12',NULL),
 (6,'a','2026-07-01','2026-09-01');

Account 1 lapses and returns. Account 2 leaves and stays inactive. Account 3 pauses collection on August 8 but keeps its entitlement, so no entitlement break appears. Account 4 cancels one overlapping plan. Account 5 is new during the month. Account 6 ends exactly at the final cutoff.

These eight rows are already curated entitlement intervals. A raw event stream would need deduplication, effective-time ordering, and business-state translation first. Keeping that assumption visible prevents the compact fixture from pretending that billing events are already a clean analytics model.

Derive the account ledger before the percentage

Use conditional aggregation to collapse all plans into two account flags. A maximum implements “at least one covering interval,” so overlapping subscriptions do not inflate the account population.

CREATE TEMP VIEW account_flags AS
SELECT account_id,
 MAX(CASE WHEN starts_at <= TIMESTAMP '2026-08-01'
   AND (ends_at > TIMESTAMP '2026-08-01'
        OR ends_at IS NULL) THEN 1 ELSE 0 END) AS active_start,
 MAX(CASE WHEN starts_at <= TIMESTAMP '2026-09-01'
   AND (ends_at > TIMESTAMP '2026-09-01'
        OR ends_at IS NULL) THEN 1 ELSE 0 END) AS active_end
FROM entitlement
GROUP BY account_id;

SELECT * FROM account_flags ORDER BY account_id;

The expected flag pairs, written as start/end, are: account 1 = 1/1; account 2 = 1/0; account 3 = 1/1; account 4 = 1/1; account 5 = 0/1; account 6 = 1/0. Inspect these classifications before accepting an aggregate result.

SELECT SUM(active_start) AS starting_accounts,
 SUM(CASE WHEN active_start = 1 AND active_end = 0
          THEN 1 ELSE 0 END) AS lost_accounts,
 1.0 * SUM(CASE WHEN active_start = 1 AND active_end = 0
               THEN 1 ELSE 0 END)
     / NULLIF(SUM(active_start), 0) AS churn_rate
FROM account_flags;

The result is five starting accounts, two lost accounts, and 0.4, or 40%. Account 5 cannot dilute the denominator because it was not present at the starting cutoff. If there are no starting accounts, the rate is NULL rather than a fabricated 0%.

Reactivation changes the event answer

Account 1 has one exit and one return within August, but zero endpoint loss. Account 2 contributes an exit and an endpoint loss. Account 4's plan cancellation is not an account exit because another entitlement continues. Account 6 contributes endpoint loss but no exit inside the half-open August event window.

Thus the fixture contains two account exits inside August: accounts 1 and 2. One of those accounts returns before September. The fact that the event count and endpoint loss count both equal two here is coincidental; their membership differs. Always compare the underlying IDs, not just aggregate totals.

For repeated churn, ask whether the numerator counts unique accounts with any exit, first exits after enrollment, or every exit episode. An account leaving twice contributes one to the first measure and two to the last. None of those definitions can be recovered reliably by counting rows whose current status is canceled.

A return is also not necessarily a new customer. If you label every plan start as acquisition, account 1 becomes a false new account and account 4's extra plan may become another. Classify starts against account-level historical coverage, including history before the reporting window.

Merge overlapping plans before counting exits

Endpoint flags need only an existence test. Transition analysis needs continuous account coverage. Sort intervals by start, calculate the maximum end among all prior rows, and begin a new coverage island only when the next start is beyond that running maximum. Treat touching intervals as continuous under our contract.

Using only LAG(ends_at) is insufficient when intervals nest. Consider one plan covering July 1–September 1, another covering July 10–July 20, and a third starting August 1. Comparing the third row only with July 20 invents a gap even though the first plan still covers August 1.

For open-ended intervals, maintain an explicit “prior interval is unbounded” flag or use an engine-supported infinity representation with documented semantics. Do not replace NULL with today's date: that turns ongoing coverage into a false historical cancellation whenever the report runs again.

After coalescing, each finite island end is a possible account exit. Each later island start is a reactivation if earlier coverage exists. Then apply the event-time window and chosen unique-account or episode aggregation. Partition additionally by product family when the business asks for product churn rather than organization-wide churn.

Pauses, incomplete windows, and late corrections

Change the pause policy and you change the intervals. If a paused plan loses entitlement, account 3 needs separate active spans before and after the pause. If pauses form a separate state excluded from churn, keep a state-transition model; removing all paused rows cannot express that distinction reliably.

Also distinguish an available snapshot from a mature follow-up window. A “churn within 30 days of first payment” metric requires either completed 30-day observations or an explicit censored-data method. The SQL retention interview exercise develops that denominator issue separately.

Late data creates another boundary: effective time determines when service changed, while ingestion time determines when the warehouse learned about it. A corrected August report and an “as known on September 1” report may legitimately differ. Preserve both timestamps and record the reporting version if reproducibility matters.

Missing snapshot rows are not universally proof of churn. They might represent a contractual absent-means-inactive convention, a failed extraction, or a delayed load. Confirm completeness before converting absence into zero. For revenue churn, additionally define currency, recurring-revenue normalization, contraction, and expansion; customer flags cannot supply those amounts.

Test the lifecycle, then optimize the query

We executed the fixture and checked every account flag and the 40% result. Duplicating an entitlement row leaves the endpoint result unchanged. Removing account 4's second plan makes that account lost and raises the rate to 60%. Moving account 6's end just after September 1 lowers endpoint loss to 20%.

Those mutations are useful interview tests because each changes exactly one business condition. Also test an interval ending at the starting cutoff, back-to-back plans, an empty starting cohort, and a cancellation request that is reversed before service ends. Explain the expected classification before executing the query.

For scale, restrict candidate intervals to those that could overlap a cutoff, retain the history required for event classification, and inspect the engine's query plan. A smaller scan is useful only if it preserves the cohort. Filtering all input to August starts would discard most of this fixture's starting customers.

Practice five related interview questions

Keep each question's stated business definitions; do not import this article's entitlement convention when a prompt explicitly specifies monthly snapshots or first cancellation.

Question	Practice focus
Write SQL for repeat churn	First qualifying exit.
Compute churn and revenue churn in SQL	Customer versus revenue loss.
Design an idempotent churn ETL pipeline	Late events and reruns.
Identify Transactions During 'Golden' Membership Period	Interval membership.
Compute paid subscriber YoY counts by month	Paid-population boundaries.

The strongest SQL churn interview answer makes the lifecycle auditable: define active service, preserve the starting population, distinguish exits from endpoint losses, and show exactly how reactivation changes the answer. Try Write SQL for repeat churn next, starting with a one-sentence metric contract rather than a SELECT statement.

Sources and Further Reading

• Stripe subscription churn overview
• Stripe cancellation documentation
• Stripe payment-collection pauses
• PostgreSQL range types and bounds

Sources checked September 21, 2026. Dataset, lifecycle classifications, and mutation tests are original practice material; local execution does not validate a production subscription integration.