How to Solve a Month-over-Month Churn SQL Interview Question

Churn questions are common in data engineering and business intelligence interviews because they test more than basic aggregation. You need to work with time periods, define when a user is active, handle open-ended subscriptions, and compare one month with another.

I added this scenario to DataCurlew, where you can practice it directly in your browser. Let’s walk through the solution.

The problem

Assume we have a subscriptions table with these columns:

subscription_id — INT
user_id — INT
start_date — DATE
end_date — DATE

An end_date of NULL means the subscription is still active. The goal is to calculate the month-over-month churn rate for every month in 2025.

A user is active during a month when:

Their subscription started before the next month.
Their subscription ended on or after the beginning of the current month, or has no end date.

The churn rate is:

   users who churned during the month
----------------------------------------
   users active in the previous month

Step 1: Create a calendar of months

Do not group only by start_date or end_date.
If no user started a subscription in March, March could disappear from the result even though many users were active that month.
A calendar CTE gives every month a row. We include December 2024 so January 2025 has a previous month for comparison.

WITH months(month_start, previous_month_start, next_month_start) AS (
  VALUES
    (DATE '2024-12-01', DATE '2024-11-01', DATE '2025-01-01'),
    (DATE '2025-01-01', DATE '2024-12-01', DATE '2025-02-01'),
    (DATE '2025-02-01', DATE '2025-01-01', DATE '2025-03-01'),
    (DATE '2025-03-01', DATE '2025-02-01', DATE '2025-04-01'),
    (DATE '2025-04-01', DATE '2025-03-01', DATE '2025-05-01'),
    (DATE '2025-05-01', DATE '2025-04-01', DATE '2025-06-01'),
    (DATE '2025-06-01', DATE '2025-05-01', DATE '2025-07-01'),
    (DATE '2025-07-01', DATE '2025-06-01', DATE '2025-08-01'),
    (DATE '2025-08-01', DATE '2025-07-01', DATE '2025-09-01'),
    (DATE '2025-09-01', DATE '2025-08-01', DATE '2025-10-01'),
    (DATE '2025-10-01', DATE '2025-09-01', DATE '2025-11-01'),
    (DATE '2025-11-01', DATE '2025-10-01', DATE '2025-12-01'),
    (DATE '2025-12-01', DATE '2025-11-01', DATE '2026-01-01')
)

Each row stores the current month, previous month, and next month.

Step 2: Find active users

Next, join each month to the subscriptions that were active during that month.
The end_date IS NULL condition keeps current subscriptions active.

active_users AS (
  SELECT DISTINCT
    m.month_start,
    s.user_id
  FROM months AS m
  JOIN subscriptions AS s
    ON s.start_date < m.next_month_start
   AND (
     s.end_date IS NULL
     OR s.end_date >= m.month_start
   )
)

DISTINCT ensures that each user appears only once per month, even if they have multiple matching subscription records.

Step 3: Find churned users with an anti-join

A user churned when they were active in the previous month but are not active in the current month.
We find those users by:
Getting the previous month’s active users.
Looking for the same users in the current month.
Counting users with no match.
This is the anti-join pattern.

monthly_churn AS (
  SELECT
    m.month_start,
    COUNT(previous_users.user_id) AS active_last_month,
    SUM(
      CASE
        WHEN previous_users.user_id IS NOT NULL
         AND current_users.user_id IS NULL
        THEN 1
        ELSE 0
      END
    ) AS churned_users
  FROM months AS m
  LEFT JOIN active_users AS previous_users
    ON previous_users.month_start = m.previous_month_start
  LEFT JOIN active_users AS current_users
    ON current_users.month_start = m.month_start
   AND current_users.user_id = previous_users.user_id
  WHERE m.month_start >= DATE '2025-01-01'
  GROUP BY m.month_start
)

If current_users.user_id is NULL, the user was active in the previous month but not the current one. That user counts as churned.

Step 4: Calculate the churn rate

The final step is to divide churned users by users active in the previous month.
The CASE expression prevents division by zero.

SELECT
  month_start AS month,
  active_last_month,
  churned_users,
  CASE
    WHEN active_last_month = 0 THEN 0.00
    ELSE ROUND(
      100.0 * churned_users / active_last_month,
      2
    )
  END AS churn_rate
FROM monthly_churn
ORDER BY month;

Multiplying by 100.0 returns a percentage and avoids integer division.

The complete query

WITH months(month_start, previous_month_start, next_month_start) AS (
  VALUES
    (DATE '2024-12-01', DATE '2024-11-01', DATE '2025-01-01'),
    (DATE '2025-01-01', DATE '2024-12-01', DATE '2025-02-01'),
    (DATE '2025-02-01', DATE '2025-01-01', DATE '2025-03-01'),
    (DATE '2025-03-01', DATE '2025-02-01', DATE '2025-04-01'),
    (DATE '2025-04-01', DATE '2025-03-01', DATE '2025-05-01'),
    (DATE '2025-05-01', DATE '2025-04-01', DATE '2025-06-01'),
    (DATE '2025-06-01', DATE '2025-05-01', DATE '2025-07-01'),
    (DATE '2025-07-01', DATE '2025-06-01', DATE '2025-08-01'),
    (DATE '2025-08-01', DATE '2025-07-01', DATE '2025-09-01'),
    (DATE '2025-09-01', DATE '2025-08-01', DATE '2025-10-01'),
    (DATE '2025-10-01', DATE '2025-09-01', DATE '2025-11-01'),
    (DATE '2025-11-01', DATE '2025-10-01', DATE '2025-12-01'),
    (DATE '2025-12-01', DATE '2025-11-01', DATE '2026-01-01')
),
active_users AS (
  SELECT DISTINCT
    m.month_start,
    s.user_id
  FROM months AS m
  JOIN subscriptions AS s
    ON s.start_date < m.next_month_start
   AND (
     s.end_date IS NULL
     OR s.end_date >= m.month_start
   )
),
monthly_churn AS (
  SELECT
    m.month_start,
    COUNT(previous_users.user_id) AS active_last_month,
    SUM(
      CASE
        WHEN previous_users.user_id IS NOT NULL
         AND current_users.user_id IS NULL
        THEN 1
        ELSE 0
      END
    ) AS churned_users
  FROM months AS m
  LEFT JOIN active_users AS previous_users
    ON previous_users.month_start = m.previous_month_start
  LEFT JOIN active_users AS current_users
    ON current_users.month_start = m.month_start
   AND current_users.user_id = previous_users.user_id
  WHERE m.month_start >= DATE '2025-01-01'
  GROUP BY m.month_start
)
SELECT
  month_start AS month,
  active_last_month,
  churned_users,
  CASE
    WHEN active_last_month = 0 THEN 0.00
    ELSE ROUND(
      100.0 * churned_users / active_last_month,
      2
    )
  END AS churn_rate
FROM monthly_churn
ORDER BY month;

The pattern to remember

For month-over-month churn questions:
Create a calendar so empty months are not lost.
Define active users carefully, including NULL end dates.
Compare previous and current users with an anti-join.
Protect the final calculation from division by zero.

You can practice this scenario on DataCurlew, which runs PostgreSQL directly in your browser.