FAQ: Calculating Churn - Multiple Month: Create Months Temporary Table

Frequently Asked Questions SQL FAQ
1

Community%20FAQs%20on%20Codecademy%20Exercises
Community%20FAQs%20on%20Codecademy%20Exercises1000×208 141 KB

This community-built FAQ covers the “Multiple Month: Create Months Temporary Table” 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: Create Months Temporary Table

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!

2

FYI I had trouble with getting the answer to the first question:

"1.

We will be using the months as a temporary table (using WITH ) in the churn calculation.

Create the months temporary table using WITH and SELECT everything from it so that you can see the structure.

We need a table for January, February, and March of 2017."

I found the concept to be pretty simply, but was stumped for awhile. Turns out it was adding a few commas that I didn’t need. I thought I would convey that here:

WITH months AS (
SELECT STATEMENT (no comma at the end)
UNION
SELECT STATEMENT (no comma at the end)
UNION
SELECT STATEMENT (no comma at the end)
)
SELECT *
FROM months;

4 Likes
3

Hi, In slide 4 I was getting an error message reading something like “be sure your table has 3 months in it” when I displayed my code. Once I decided to let Codecademy give me their answer, I realized the only difference was that my February row’s “last_day” was 31 while the answer’s was 28.

Suggesting that a reminder about the number of days in Feb may be a better hint for that issue. Thanks for the new path!

10 Likes
4

Dude thank you lol I was struggling all over forgetting the length of the month of February. I think the most valuable lesson here for a new programmer is that paying attention to detail needs to be brought to a WHOLE other level. Thanks for the tip mckinley

4 Likes
5

hi, why we not use CREATE TABLE and INSERT INTO? for new table?

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;

6

hi, why we not use CREATE TABLE and INSERT INTO? for new table?

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;

7

I was thinking to split rows into months columns with something like:

select * 
from subscriptions
group by strftime('%Y %m', subscription_start);

can you tell me why it does not work as expected ?

8

What am I doing wrong with my code here?

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;

9

your quotation marks

10

awesome thanks for the feedback

11

Wow thank you! I totally didn’t realise this for a long time.

12

Hi there, I think that’s because we need temporary table and ‘WITH’ is the way.

13

Is there another way to create a temporary table from scratch? To me the UNION statement does not seem that convenient.

Is there a possibility to include the INSERT INTO Statement into a temporary table created by the WITH statement? I`ve tried to…emphasis on tried :smiley: what am I doing wrong?

WITH months AS(
SELECT ‘2017-01-01’ AS first_day, ‘2017-01-31’ AS last_day
INSERT INTO months (first_day, last_day)
VALUES
(‘2017-02-01’,‘2017-02-28’),
(‘2017-03-01’,‘2017-03-31’)
)
SELECT * FROM months;

14

What about February 29? :thinking:

15

Agree this is probably the most important lesson to be learned. :grin: All the times I got stuck it turned out to be because of some tiny error, like a . instead of _ or an upper case instead of lower case letter. In the last exercise with the glasses I got stuck with writing if number_of_pairs = “3” and couldn’t make the code work for an eternity, until I realised in the table it said “3 pairs”. Sigh!

16

A bit confused about about the aliasing in this exercise, why do we write ( SELECT “2017-02-01” AS first_day ) here, but (SELECT COUNT(*) AS “First step”), in other SELECT queries? To be clearer, why do we need the quotation marks sometimes when aliasing and sometimes not?

2 Likes
17

I think i got the bit to setup the dates, ok but incase i leap year i had 29 days in february not too worry, this is the solution you need months at the top with months as and then
SELECT * select all table columns with the star

FROM months; from months using the semi colon to end statement…

Thanks

Ian

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; semi colon

18

I was wondering this too but it is only in a leap year , so my guess is too make it simpler it is 28 days thanks
Ian

19

my guess is this is not a temporary table that you are making , you are trying to select year and month from all columns from the subscriptions table imho

thanks

Ian

I think this is what you mean

20

CORRECT SOLUTION:

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;