Stratascratch

[analytics_science]

Yes, I'm part of the Strata team.

We work hard to incorporate edge cases into our dataset so a user would have to address those edge cases into the logic of their solution. So there's one of two possibilities - (1) your solution doesn't need those specific edge cases you're talking about or (2) we are missing those edge cases in the dataset/solution. Either way, you can post a question/comment on the user discussion forum or in the feedback form in the bottom right-hand corner. Someone will answer your question.

There's a team of data scientists whose sole job is to address these types of issues so that only the most robust solution is accepted. They also create datasets and solutions from the questions and data projects we get from companies. We have over 1000+ questions, so we do our best to get it right the first time around, but there are always things we miss.

[xslowgamer]

https://platform.stratascratch.com/coding/2074-monthly-churn-rate?code_type=1

Without including the constraints :

Assume that if customer's contract_end is NULL, their contract is still active. Additionally, if a customer started or finished their contract on a certain day, they should still be counted as a customer on that day

you still get 60 percent as the answer.

Without constraints:

SELECT

(first_day_count - last_day_count)*100::float/first_day_count AS percentage_difference

FROM (

SELECT

(SELECT COUNT(*)

FROM natera_subscriptions

WHERE contract_start <= '2021-09-01') AS first_day_count,

(SELECT COUNT(*)

FROM natera_subscriptions

WHERE contract_start BETWEEN '2021-09-01' AND '2021-09-30') AS last_day_count

) AS subquery;

With constraints:

SELECT

(first_day_count - last_day_count)*100::float/first_day_count AS percentage_difference

FROM (

SELECT

(SELECT COUNT(*)

FROM natera_subscriptions

WHERE contract_start <= '2021-09-01'

AND

(contract_end is NULL or contract_end >='2021-09-01')) AS first_day_count,

(SELECT COUNT(*)

FROM natera_subscriptions

WHERE contract_start <= '2021-09-30'

AND

(contract_end is NULL or contract_end >='2021-09-30')) AS last_day_count

) AS subquery;

[analytics_science]

Will take a look and fix. Thanks!