Adding An Age Column with Values Based on Birthdays in Another Column

Data Science
1

I’m working my way through a SQL Challenge Project in the Data Science Career Path and seem to be stuck on a final extra problem

Basically we have a column that is birthdates and they want us to add a column that calculates a persons age based on this.

After some Googling I learned about DATEDIFF but I can’t seem to use it right. My code is below as follows

#Adds the age column to the table
ALTER TABLE users
ADD COLUMN age int NULL;

#Attempting to update the table by setting the age column to the #DATEDIFF function
UPDATE users
SET age = DATEDIFF(year, birthday, GETDATE());

#Trying to view the table to see if it worked
SELECT *
FROM users;

I’ve found the error is in the Updating part but i can’t seem to find the problem.

Any help or guidance would be greatly appreciated!

1 Like
2

Hi @web1886190748,

Can you please post a link to the project you are talking about? There is a lot of content on Codecademy and even if we know which career path, it can take us volunteers quite some time to find the exact project/exercise if you don’t provide a link in your post.

Thanks!

1 Like
3

Of course! Sorry about that!

The project is called RPA Customer Segmentation. It is in the Data Scientist Career Path under SQL Challenge Projects.

I’m specifically working on Question 8 of that Challenge Problem which states “One of the members of the marketing team had an idea of calculating how old users were when they signed up”

Hopefully that helps but if I can offer more specific info let me know!

1 Like
4

Any chance you could directly link the lesson, i.e. copy/paste the address. Hunting down specific projects through cc’s catalogue can be quite difficult.

The only project I noted with a similar title has only 7 tasks to complete.

1 Like
5

Same here. I only have 7 questions in that project on the DS path.
https://www.codecademy.com/paths/data-science/tracks/dscp-data-acquisition/modules/dscp-sql-challenge-projects/projects/user-segmentation-prj

6

https://www.codecademy.com/paths/data-science/tracks/dscp-data-acquisition/modules/dscp-sql-challenge-projects/projects/user-segmentation-prj

Here is the link. The 8th Challenge question isn’t on the left side like normal. It is in the terminal window at the very bottom of the terminal. It is a comment as an extra challenge.

1 Like
7

Why is NULL in the ADD COLUMN portion?

ALTER TABLE table_name  
ADD column_name column_definition;

Shouldn’t it be:

ALTER TABLE users
ADD COLUMN age INTEGER;

?

8

I was trying to follow a guide on DateDiff and they used that notation so I was admittedly taking it for granted.

I actually ended up changing it to just INT in a later attempt

1 Like
9

I don’t have a question 8 in that project. When I completed it, it was 7/7.

How about the last response example here:

https://stackoverflow.com/questions/51101976/insert-datediff-result-into-a-column-in-table

10

This is “question 8”:

-- 8
-- Challenge
-- One of the members of the marketing team had an idea of calculating
-- how old users were when they signed up.

It’s a comment at the bottom of the code editor. :slight_smile:

1 Like
11

Gotcha. but, still not on my version of this project. :slight_smile:

1 Like
12

I think you’re making this query waaaaay more difficult that it needs to be. :slight_smile:

Couldn’t we use a simple calculation on two columns and then use AS?

2 Likes
13

That looks to along the lines of what I’m trying to do. I’ll check it out!

1 Like
14

You don’t need to alter the table either…as using an alias adds a column.

1 Like
15

Perhaps that might be where I’m struggling, it’s how to do the simple calc on columns because they’re dates.

My thinking is take the date now - birthday and use that as the value of age column for the row.

It’s the translation to SQL coding that I can’t seem to get around but I’ll keep plugging away at it

16

I’m in the same boat where I only saw 7 questions (if there was an eighth as a comment then I must’ve somehow missed it).

It’s worth noting that there’s no DATEDIFF in the sqlite docs (so avoid sinking time into trying it for this question) so unless there’s something I’m missing you’d need to work with the date in numerical form. I think I worked this one out with strftime but julianday is another option. Perhaps there’s a better route but I couldn’t spot one for sqlite.

1 Like
17

Oh! That might be it, thank you for that!

18

Date calculations can be tricky. I tried using the strftime and julianday functions and they didn’t quite calculate the ages correctly. For example, the birthdate of 1982-04-17 came out to 39 years old instead of 38.

Finally, I got this to work:

select email, birthday, cast(strftime(’%Y.%m%d’, ‘now’) - strftime(’%Y.%m%d’, birthday) as int)
from users
limit 5;

Good job on trying the challenge question! :star2:

8 Likes
20

Thanks everyone for your help! Ended up finding a video about strftime that really helped me and the code he helped explain was. @coffeencake your code is must quicker but I’m not familiar with the CAST clause yet haha

SELECT birthday,
  CASE
    WHEN strftime('%m', date('now')) > strftime('%m', date(birthday))
      THEN strftime('%Y', date('now')) - strftime('%Y', date(birthday))
    WHEN strftime('%m', date('now')) < strftime('%m', date(birthday))
      THEN strftime('%Y', date('now')) - strftime('%Y', date(birthday)) - 1
    WHEN strftime('%m', date('now')) = strftime('%m', date(birthday)) THEN
      CASE
        WHEN strftime('%d', date('now')) >= strftime('%d', date(birthday))
          THEN strftime('%Y', date('now')) - strftime('%Y', date(birthday))
        ELSE strftime('%Y', date('now')) - strftime('%Y', date(birthday)) - 1
      END
  END AS 'age'
FROM users;

Kudos to Calculate Age in SQLite | SQL Bits | Kovolff - YouTube for the help!

9 Likes
21

I like the shorter formatting version with %Y.%m that I’d not come across. The only bit I’d note there is that the quote above mentions "how old users were when they signed up" so you may want to slightly alter the code to match that.

2 Likes