User Segmentation Challenge Project - Data Scientist Path

Get Help SQL
#1

Hello all,

I am currently working my way through the SQL part of the Data Scientist Career Path.

I just completed the “user segmentation” project.

There is a extra challenge at the end, but unfortunately the solution is not included at the walkthrough solution video.

The description of the challenge is the following:

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

You can find the schema of the database here.

I would really appreciate if someone could point out on possible solutions for this.

Thank you in advance for your help!

#2

Since it’s optional it might be worth giving it a shot. Chances are you’ll rely on at least one date based function (if you’re unfamiliar with them you may need to search for them online but make sure it’s sqlite based).

I’d still advocate for giving it a go but there are at least two fairly large threads about this project and the last question on the forums if you search for them.

2 Likes
#3

Hello @tgrtim ,

Thank you for taking the time to help.

I tried some things but I can’t figure out the last step:

The below query shows the age of users:

SELECT created_at - birthday INT 
FROM users
LIMIT 10;

I tried various things such as the query below, but I can’t figure out how to add such a column to the existing table! What am I missing here?

#ALTER TABLE users
#ADD COLUMN created_at - birthday INT;

Thank you again for your help!

#4

I don’t know if actually adding a new columns is a required step but maybe it’s worth trying anyway. I think the sqlite norm is to add the column in the way you have done (choose a sensible name) and then INSERT INTO that newcolumn the values you actually want. You might not be doing this very often. Creating new tables (or temporary tables) is one thing but altering existing tables this way is rare.

I do think it’s worth noting that human age is typically floored instead of rounded, e.g. 8 months old is not a 1 year old and 29 years 312 days old is still 29 years old (this often extends into the legal system too, e.g. age for driving). If you’re querying ages be very careful about whether you want a floored or a rounded age.

1 Like
#5

Hello @cspanias ,

You don’t need to add a column to the table and your SELECT statement is pretty close.

It’s a long thread and a good discussion on the challenge question. There were different interpretations indeed.

I think the simplest solution is to use the DATE() function on the created_at and birthday columns.

1 Like