LeetCode Bug: Test Case Errors & Churn Risk

Hey guys, this is a breakdown of a frustrating LeetCode problem, where a missing test case allows for incorrect solutions to be accepted. We'll dive into the details, focusing on how a crucial test case reveals flaws in the problem's current setup. This issue highlights the importance of comprehensive testing in coding challenges and the potential for incorrect solutions to slip through the cracks. It's a great example of how important thorough testing is! Let's get into it.

The Problem: Identifying Churn Risk

The original problem is called "Find Churn Risk Customers" (LeetCode problem 3716). The goal is to identify users who are at risk of churning, or canceling their subscription. It is important to know which customers are going to leave because this will help the company to react in time.

This involves analyzing subscription events to find users who meet certain criteria, such as downgrading their plan, having a significant difference between their maximum historical monthly amount and their current amount, and the time they have been a subscriber. Basically, we need to know who is unhappy and likely to leave. The initial code, however, has a critical flaw: It doesn't handle multiple 'cancel' and 'start' events on the same day correctly.

The Bug: Multiple Events on the Same Day

The core of the problem lies in how the code determines the latest event. The code provided uses only the event date. In real-world scenarios, multiple events can occur on the same day. For example, a user might cancel their subscription and then restart it later in the same day. The original code's reliance on only the event date, without considering a tiebreaker like the event ID, leads to incorrect results.

This oversight is significant because it can lead to misclassifying users and providing inaccurate churn risk assessments. The suggested solution emphasizes the need for a more robust method of identifying the latest event, especially when multiple events occur on the same day.

The Missing Test Case and Its Impact

The heart of the issue is the lack of a test case that specifically addresses this flaw. The provided test case is designed to expose the problem. This test case includes a scenario where a user (ID 504) has multiple 'cancel' and 'start' events on the same day. The expected behavior is that the solution should correctly identify the user's current status based on the latest event, whether it's a 'start' or 'cancel'.

Because the existing test cases don't cover this, many incorrect solutions are accepted. These solutions incorrectly identify the latest event and, therefore, provide wrong information on who is going to leave. So sad!

The Proposed Solution: Correct Event Identification

The solution to this problem is to correctly identify the latest event. The proposed solution uses both the event date and the event ID to determine the order of events. The key part is using the row_number() window function to assign a rank based on event date and event ID. This correctly identifies the most recent event, even if multiple events occur on the same day. Using the event ID ensures that the order of the events is maintained, which fixes the problem!

Code Examples

Let's examine the code and see how it works to get a clearer understanding. Here is the initial (incorrect) code that was accepted:

with UserStats as (
    select user_id, 
    min(case when event_type = 'start' then event_date else null end) as "start_date",
    max(event_date) as "last_event",
    max(monthly_amount) as "max_revenue",
    sum(event_type = 'downgrade') as "downgrade_count"
    from subscription_events
    group by user_id
)
select us.user_id,
se.plan_name as "current_plan",
se.monthly_amount as "current_monthly_amount",
us.max_revenue as "max_historical_amount",
datediff(us.last_event, us.start_date) as "days_as_subscriber"
from UserStats us join subscription_events se on us.user_id = se.user_id and us.last_event = se.event_date
where se.event_type <> 'cancel' and
us.downgrade_count > 0 and
se.monthly_amount * 2 < us.max_revenue and
datediff(us.last_event, us.start_date) > 60
order by datediff(us.last_event, us.start_date) desc, us.user_id asc;

This code has issues because it does not properly handle events on the same date.

Now, here is the corrected solution, which includes the event ID in determining the latest event:

with all_events as ( 
    select
        user_id,
        event_id,
        event_date,
        event_type,
        plan_name,
        monthly_amount,
        row_number() over (
            partition by user_id
            order by event_date desc, event_id desc
        ) as rn_latest,
        max(monthly_amount) over (partition by user_id) as max_revenue,
        max(case when event_type = 'downgrade' then 1 else 0 end)
            over (partition by user_id) as has_downgraded,
        min(event_date) over (partition by user_id) as first_event_date,
        max(event_date) over (partition by user_id) as last_event_date
    from subscription_events
),
candidates as (
    select
        *,
        datediff(last_event_date, first_event_date) as days_as_subscriber
    from all_events
    where rn_latest = 1
      and event_type != 'cancel'
      and monthly_amount < 0.5 * max_revenue
      and has_downgraded = 1
      and datediff(last_event_date, first_event_date) >= 60
)
select
    user_id,
    plan_name as current_plan,
    monthly_amount as current_monthly_amount,
    max_revenue as max_historical_amount,
    days_as_subscriber
from candidates
order by days_as_subscriber desc, user_id asc;

The corrected solution uses the row_number() function with the event_id to make sure it identifies the latest event!

The Importance of Thorough Testing

This issue underscores the importance of having comprehensive test cases. The initial problem's acceptance of incorrect solutions highlights the potential for flaws to go unnoticed without the right test coverage. This isn't just a LeetCode issue; it's a broader lesson in software development. Good testing leads to more reliable code.

Recommendations

To address this, LeetCode should either add a test case with multiple events on the same day or include a constraint that restricts the number of events per day. The current lack of this crucial test case allows incorrect solutions to be accepted, undermining the integrity of the problem and the learning process. Hopefully LeetCode will consider the feedback and update the problem. Thanks for reading guys!"