월간 이탈 고객 (churn) SQL 면접 질문을 해결하는 방법

작성자

카테고리:

DEV Community · Rahman · 2026-09-06 개발(SW)

Posted on Sep 5 AI-assisted

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

Enter fullscreen mode Exit fullscreen mode

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')
)

Enter fullscreen mode Exit fullscreen mode

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
   )
)

Enter fullscreen mode Exit fullscreen mode

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
)

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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.

원문에서 계속 ↗

추출 본문 · 출처: dev.to · https://dev.to/rahmanfrr/how-to-solve-a-month-over-month-churn-sql-interview-question-1abo

더 많은 게시물

에피소드 1: “세상에 나타난 작은 점”

2025-08-01