Calculating Churn Rates - Query doesn't run

You must select a tag to post in this category. Please find the tag relating to the section of the course you are on E.g. loops, learn-compatibility

When you ask a question, don’t forget to include a link to the exercise or project you’re dealing with!

If you want to have the best chances of getting a useful answer quickly, make sure you follow our guidelines about how to ask a good question. That way you’ll be helping everyone – helping people to answer your question and helping others who are stuck to find the question and answer! :slight_smile:

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/projects/calc-churn-proj

I feel myself slowly going insane trying to understand why this isn’t running. It’s not throwing any errors on the RHS panel to guide me so kind of fumbling in the dark at the moment!

WITH months AS 
(SELECT 
'2017-01-01' as first_day,
'2017-01-31' as last_day
UNION
SELECT
'2017-02-01' as first_day,
'2017-02-28' as last_day
UNION
SELECT
'2017-03-01' as first_day,
'2017-03-31' as last_day
),

cross_join AS 
(SELECT *
FROM subscriptions
CROSS JOIN months
), 

status AS 
(
SELECT id, first_day AS month,
CASE
  WHEN 
   (subscription_start < first_day) AND
   (subscription_end > first_day OR subscription_end IS  NULL) AND (segment = 87) THEN 1
  ELSE 0 
  END as is_active_87,
CASE
  WHEN 
 (subscription_start < first_day) AND
 (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 30) THEN 1
  ELSE 0 
  END AS is_active_30,
   CASE 
   WHEN (segment = 87) AND
   (subscription_end BETWEEN first_day AND last_day) THEN 1
    ELSE 0
  END AS is_canceled_87,
  CASE
   WHEN (segment = 30) AND
     (subscription_end BETWEEN first_day AND last_day) THEN 1
    ELSE 0
  END AS is_canceled_30
  FROM cross_join
),

status_aggregate AS 
(
  SELECT SUM(is_active_87) AS sum_active_87,
  SUM(is_active_30) AS sum_active_30,
  SUM(sum_canceled_87) AS sum_canceled_87,
  SUM(is_canceled_87) AS sum_canceled_30
  FROM status
  GROUP BY month, segment
)

SELECT
  month, 
  1.0 * sum_canceled_87 / sum_active_87 AS churn_rate,
  1.0 * sum_canceled_30 / sum_active_30 AS churn_rate

FROM status_aggregate;

Hi,

Welcome to the forums and thanks for sharing!

Can you re-paste this in clicking the </> button before you paste to format it? It’ll be easy for other users to troubleshoot this.

If you have this much code I would work from the beginning commenting out all the other code and work your way to the end. There’s probably a simple missing element but since it’s all stacked it might be hard to notice.

A way around this is that every time you have a chunk of clauses, just check that they’re coming out as you want (as you write it).

Hi @anthonymarriage,
You won’t see a result b/c you’re not querying the CTE. I can guarantee that you’re not going insane. :slight_smile: It just feels that way. haha.
Did you happen to watch the “Get Unstuck” video to verify that your code is correct?

The console won’t print out your entire solution b/c you’re creating CTEs in steps 3-7 and you’re not directly querying those so you won’t see any output until step 8.

One way to see your results is right after you create the CTE ( WITH statement) you could query it (like is done in the video) with a SELECT * FROM months .

Also, this might be helpful, if you do a search for “User churn” you will find several threads dedicated to this exact topic. There is also an FAQ.

https://discuss.codecademy.com/t/calculating-churn-rate-code-review-feedback/518177

1 Like

Thanks for taking the time to reply!

That’s a good idea, will get back to you with an update…

I’ve been using this technique suggested:

WITH months AS 
(SELECT 
'2017-01-01' as first_day,
'2017-01-31' as last_day
UNION
SELECT
'2017-02-01' as first_day,
'2017-02-28' as last_day
UNION
SELECT
'2017-03-01' as first_day,
'2017-03-31' as last_day
),

SELECT * FROM months

To go through each section but each time I run it nothing on the right happens, it just says run a query to see results

I assume that you removed the comma following your CTE and added a “;” after your
SELECT statement?

2 Likes

There’s also the FAQ on this project here that might be of some use:
https://discuss.codecademy.com/t/faq-calculating-churn-multiple-month-cross-join-months-and-users/373459/10

Ok so update here, quite a few errors to clean up!

I’ve now got it in a state where this returns values:

WITH months AS 
(SELECT 
'2017-01-01' as first_day,
'2017-01-31' as last_day
UNION
SELECT
'2017-02-01' as first_day,
'2017-02-28' as last_day
UNION
SELECT
'2017-03-01' as first_day,
'2017-03-31' as last_day
),

cross_join AS (SELECT * FROM months
CROSS JOIN subscriptions
),

status AS 
(SELECT id, 
first_day AS month,
CASE
  WHEN 
   (subscription_start < first_day) AND
   (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 87) THEN 1
  ELSE 0 
  END AS is_active_87,
CASE
  WHEN 
 (subscription_start < first_day) AND
 (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 30) THEN 1
  ELSE 0 
  END AS is_active_30,
   CASE 
   WHEN 
   (subscription_end BETWEEN first_day AND last_day) AND (segment = 87) THEN 1
    ELSE 0
  END AS is_canceled_87,
  CASE
   WHEN (subscription_end BETWEEN first_day AND last_day) AND (segment = 30) THEN 1
    ELSE 0
  END AS is_canceled_30
  FROM cross_join
), status_aggregate AS 
(SELECT 
  SUM(is_active_87) AS sum_active_87,
  SUM(is_active_30) AS sum_active_30,
  SUM(is_canceled_87) AS sum_canceled_87,
  SUM(is_canceled_30) AS sum_canceled_30
  FROM status
  GROUP BY month
) SELECT * FROM status_aggregate;

However, when I replace the last query with the following it doesn’t work…

SELECT month, 
  1.0 * sum_canceled_87/sum_active_87 AS churn_rate_87,
  1.0 * sum_canceled_30/sum_active_30 AS churn_rate_30
FROM status_aggregate;

Good on you for debugging your code! :slight_smile:

I think you have to put the aliases in quotes.(?)

1 Like

That didn’t seem to work. Is there a reason those aliases would be in quotes? I’ve not done that in previous code, e.g.

SUM(is_active_87) AS sum_active_87,

Thank you so much for your help here!

1 Like

Did you have a month column in status_aggregate? I think it’s missing

status_aggregate AS 
(SELECT 
  SUM(is_active_87) AS sum_active_87,
  SUM(is_active_30) AS sum_active_30,
  SUM(is_canceled_87) AS sum_canceled_87,
  SUM(is_canceled_30) AS sum_canceled_30
  FROM status
  GROUP BY month
) SELECT * FROM status_aggregate;

SELECT month, 
  1.0 * sum_canceled_87/sum_active_87 AS churn_rate_87,
  1.0 * sum_canceled_30/sum_active_30 AS churn_rate_30
FROM status_aggregate;
1 Like

Oh, jeez. I’m sorry. I looked at my code for that project and that’s what I’d done and it worked.

1 Like

ding ding ding!

It’s working now, thank you again!

Is it common not to have error text for why the query isn’t running?

2 Likes

I think the codecademy sql workspace leaves a lot to be desired. For these type of exercises, I used a blend of sqlite3, DB Browser, and Sublime text editor. Maybe the initial setup takes a bit more, but I think it’s worth it when it comes to troubleshooting (better error messages).

That being said, I still got my sql bearings with these exercises, and I’m grateful it was at least an introduction. Now I’m doing a lot of sqlalchemy with python and that’s a whole other world of hurt (luckily I now have some good references).

I also have some reference books on db theory that I have to start reading…

Tl;dr: you should normally get errors, and they are usually descriptive.

2 Likes

Agreed, on the SQL setup not being ideal. Not sure if its due to all the blocking extensions I have on Safari but I don’t get error messages of any kind. The only time something displays on the RHS is when the query runs successfully. Not really ideal for earning.

Like you said its a good intro to the concepts but will need to continue using in an applied setting with SQLite.

Good luck with the sqlalchemy!

Also, maybe use Chrome with CC. I think it works a bit better. (Or, it does in my experience).

I use(d) Sqlalchemy with Colab and a PgAdmin server (using Postgres). I assume you used psycopg2 to make the db connection too?
It was def. a learning curve and I’m still learning!

No, I’m doing it through flask. I think I’m over the first “completely-lost-in-the-forest” hill.

Edit: Looking up psycopg2 … definitely not what flask does hahahah.

I have the same problem. My code don’t give any result and error message. I have read the comments
but it doesn’t work for my code.

WITH
months AS
(
SELECT
“2017-01-01” as first_day,
“2017-01-31” as last_day
UNION
“2017-02-01” as first_day,
“2017-02-30” as last_day
UNION
“2017-03-01” as first_day,
“2017-03-31” as last_day
),
cross_join AS
(
SELECT *
FROM months
CROSS JOIN subscriptions
),
status AS
(
SELECT id, first_day AS month,
CASE
WHEN (subscription_start < first_day)
AND (
(subscription_end > first_day)
OR (subscription_end IS NULL)
)
AND (segment= 87)
THEN 1
ELSE 0
END AS is_active_87,
CASE
WHEN (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
)
AND (segment= 30)
THEN 1
ELSE 0
END AS is_active_30,
CASE
WHEN (subscription_end BETWEEN first_day AND last_day)
AND (segment= 87)
THEN 1
ELSE 0
END as is_canceled_87,
CASE
WHEN (subscription_end BETWEEN first_day AND last_day)
AND segment = 30
THEN 1
ELSE 0
END as is_canceled_30,
FROM cross_join
),
status_aggregate AS(
SELECT
SUM(is_active_87) AS “sum_active_87”,
SUM(is_active_30) AS “sum_active_30”,
SUM(is_canceled_87) AS “sum_canceled_87”,
SUM(is_canceled_30) AS “sum_canceled_30”
FROM status
GROUP BY month
)
SELECT month,
1.0 * sum_canceled_87 / sum_active_87 AS “churn_rate_87”,
1.0 * sum_canceled_30 / sum_active_30 AS “churn_rate_30”
FROM status_aggregate;

When there are this many steps, sometimes it’s helpful to print out (or, in this instance SELECT *, etc.) each bit of code to see if it’s working.

February doesn’t have 30 days.

Also, your CROSS JOIN is incorrect. Re-read q4: " Create a temporary table, cross_join , from subscriptions and your months . Be sure to SELECT every column."

And, your last CASE statement is missing some parens: