FAQ: Calculating Churn - Multiple Month: Cross Join Months and Users

This community-built FAQ covers the “Multiple Month: Cross Join Months and Users” exercise from the lesson “Calculating Churn”.

Paths and Courses
This exercise can be found in the following Codecademy content:

FAQs on the exercise Multiple Month: Cross Join Months and Users

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

I’m clearly missing something extremely basic here - can anyone help out! Thanks

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
),
– Add temporary cross_join definition here
temptable AS (
SELECT *
FROM subscriptions
CROSS JOIN months
)
LIMIT 10;

Doh, forgot to SELECT at the end… Also requires LIMIT 100 to pass.

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
),
– Add temporary cross_join definition here
cross_join AS (
SELECT *
FROM subscriptions
CROSS JOIN months
)
SELECT * FROM cross_join
LIMIT 100;

2 Likes

Does the table order matter for CROSS JOIN operator?
tried

(SELECT *
FROM months
CROSS JOIN subscriptions)

but it validated my answer as wrong although the query result was correct.

1 Like

Hi Codecademy, I was very frustrated with this exercise.
Answer had little to no relevance with the instructions given.

Review:
This exercise gets a 2/5 for me. Instructions were clear yet did not address the actions I needed to take concretely to achieve the answer. I did get prompts, but did not get me any closer to the answer. Answer I only understand now in hindsight. I hope I will be able to keep progressing in the rest of the lessons.

Instructions:
The workspace contains the months temporary table from the previous exercise.

Create a cross_join temporary table that is a CROSS JOIN of subscriptions and months .

We’ve added:

SELECT * FROM cross_join LIMIT 100;

at the bottom of this exercise so you can visualize the temporary table you create.

It should SELECT all the columns from the temporary table.

My input: I was trying to achieve a join and a rename in separate steps and also to fulfill the prompt of seeing I needed to create a table with 2 categories for months (first_day and last_day) followed by all of the 3 for subscriptions (which were not in key words)

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 months.first_day, months.last_day
FROM months AS cross_join
CROSS JOIN subscriptions,
SELECT *
FROM cross_join
LIMIT 100;

Exercise did not show how to rename produced temporary tables let alone give pertinent syntax in the hint:

The pattern for creating a temporary table using a CROSS JOIN is:
desired_temp_table AS (SELECT * FROM table1 CROSS JOIN table2)

The answer did not use the format of the hint. The hint was useless and frustrating. I did not learn from the hint and the hint did not help me get the answer. I spent 1 hour on this post and the exercise, trying my utmost to understand the concepts, mechanics, and not default to the solution. I am sure there will be more for me to say overtime. I’ll continue to move forward.

6 Likes

Can somebody please tell me why CROSS JOIN is done ! THANKS

2 Likes

Hi there, it basically change the places of columns, it maybe ok for you but this exercise specifically want it to be in this order. I did the same mistake, and I hope :crossed_fingers: I could help.

1 Like

why did we not use a WITH statement on temptable

WITH temptable AS

4 Likes

Hello, i believe we use cross join, as there is a possibility that there could be multiple results for a subscription id. I.e) hypothetically, if the range of time used was a year it is possible that a user or subscription_id could be active or cancelled multiple times. Using cross join will give all possible combinations. However based on the data set used in this exercise this situation would not appear.

1 Like

I think I’m missing something here.

The code to create a temp table by doing CROSS JOIN is below, but why we didn’t do it

WITH cross_join AS ().

The correct code is:

cross_join AS

(SELECT * FROM months

CROSS JOIN subscriptions

)

SELECT *

FROM cross_join

LIMIT 100;

I didn’t get when to use WITH and when not to use it while creating a temp table. Can someone help pls?

Yes, just swap the positions of months and subscriptions and you’ll be fine.

The following code worked for me:

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)

SELECT *
FROM cross_join
LIMIT 100;

Happy coding!

The reason you only need to use WITH once is because there is a comma underneath the definition of the first temp table. You are still able to keep defining other temp tables under that one WITH.

2 Likes

Hello, I am confused on the results from this part. I understand that it all works and makes sense but if in the previous part we needed to add a ‘with’ to create the temporary table months, why in this next part does cross_join not need a ‘with’ to create it? When is ‘with’ necessary, if ever since that temporary table was created regardless?

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

),

– Add temporary cross_join definition here
–why is there no with before the cross_join??
cross_join as (

select * from subscriptions cross join months

)

SELECT *

FROM cross_join

LIMIT 100;

Oh, that was my question too. So one ‘WITH’ consecutively before any other temporary table will keep defining the ones under it? If instead of having them on consecutive lines like the correct answer does, the cross_join table was listed after a ‘SELECT’ then another ‘WITH’ would be needed, is that correct?

for example:
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

LIMIT 100;

–Would you include WITH at this point

cross_join as (

select * from subscriptions cross join months

)

Hi there,

I can’t understand what’s the main sense of creating this table.

I understand how it’s been created, but not why.

For example, why the subscription_end and last_day are different?

I thought last_day was meant for the last day of subscription.

I have the exact same question about this! Hopefully someone can provide an answer!

Said another way,

WITH
table1 AS (…),
table2 AS (…)

SELECT table1.useful_info, table2.other_useful_info
FROM table1
CROSS JOIN table2

The ‘WITH’ function applies to both temp tables and it is not until you get to the SELECT at the bottom that you are actually creating a table you can see. The rest is just amassing data for reference in your final SELECT process.

If you’re still having trouble, work from the end. What do you want you final SELECT section to read? What tables are you referencing? Can you build your code backwards knowing what you are solving for and how many tables that would require?

Hope that helps

1 Like

Hey! Anyone find this exercise a little redundant on the use of WITH? Rather than having two temp tables, we can have just one, cross join directly, and alias our final table’s name. The code below registers as a pass for the exercise:


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 subscriptions as cross_join

cross join months

LIMIT 100;


… compare this to the solution Codecademy wants you to reach:


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)

SELECT *
FROM cross_join
LIMIT 100;


Can anyone suggest a reason why we might want the second approach (Codecademy’s approach) over the first and simpler approach other than, perhaps, readability or a more step-by-step process? Thanks!

Capture 21