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

Thanks @tgrtim! I honestly didn’t read that carefully.

Cheers to everyone who helped out on this thread!

3 Likes

Oh, I had to do my share of searching too. I was originally thinking along the same lines that you had in the case statement but thought that was ALOT of work to get the age of someone. :grinning_face_with_smiling_eyes:

2 Likes

Thank you a lot !, I really did not have idea how to solve the challenge

1 Like

Thank you very much!
Your link and your solution helped me a lot.

The only problem with your solution (and coffeencakes as well) is that the question is not about the current age of users but:
how old users were when they signed up”.

But it’s easy now - just change date(‘now’) to date(created_at).

I tried with the simple:

SELECT email
, date(created_at) - date(birthday)
FROM users;

but it calculates again only difference in years, not caring about moths and dates

Thanks for help!

P.S. The question is about age when signing up not current age.

2 Likes

Oh! Thanks for pointing that out. Sometimes I make my own problems :slight_smile:

1 Like

@lisalisaj had a great answer. Here’s a little more to go on.

I got this to work with a simple SELECT … FROM … query using julianday()

Try playing with this format:

SELECT calculation AS new_column
FROM table;

example:

SELECT cost_1 - cost_2 AS cost_difference
FROM price_chart;

cost_1 and cost_2 are columns in price_chart. This creates a new column, cost_differnce, containing the result of the calculation.

This is not the complete solution as I believe a case statement should be used. But this is working for me

SELECT cast((julianday(created_at) - julianday(birthday)) / 365 as int) as registered_age
FROM users
LIMIT 10;

Without the cast statement, the results appear with decimals
The cast statement takes the value and “converts it into an int” and rounds like a FLOOR function.

1 Like

This line makes the most sense and its the shortest :upside_down_face:
However, currently on my path I have not learned CAST or julianday.

Thanks!

Makes sense, however gets a little complicated with two case operators. I watched the same video as you and the creator explains it pretty well. Just something I am going to have to refresh my memory on if I have similar problem in the future :sweat_smile:

I did the following which gave me the result I was looking for. Combination of users above code.

SELECT *, created_at - birthday AS age_at_signup

FROM users

:slight_smile:

1 Like

Hi,
I was able to overcome this task with this simple and clean code.

SELECT CAST(julianday(created_at) - julianday(birthday) as integer) / 365 FROM users;
1 Like

Hi,
I was able to overcome this task with this simple and clean code.

SELECT CAST(julianday(created_at) - julianday(birthday) as integer) / 365 FROM users
2 Likes

I just made a new column (age_on_signup) by calculating difference between birthday and created_at column.

SELECT *, (created_at - birthday) as age_on_signup
FROM users

I just got a new column (age_on_signup)

I am wondering in the code you shared, why we need to / 365?

1 Like

Hi, I was wondering why we could not use a simple statement such as below to solve the challenge problem :

I just made a new column ’ age_on_signup’ and just calculated difference between birthday and created_at column, using the code below :

SELECT *, (created_at - birthday) as age_on_signup
FROM users

1 Like

Folks typically state their age based on a floored value of years they’ve been alive. Take for example a child 8 months old. You would not call them a 1 year old. Under the same standard someone who has born 59 years and 200 days ago would be 59, not 60. At present your query rounds ages instead of flooring them (at 59.5 the output gives you 60 instead of 59).

Consider for example if you made this query and sent marketing emails or similar based on that output to your users (stating a good portion of them are older than they actually are) you could end up with some complaints.

Regarding the julianday question that is because it creates a value based on days and you are interested in an output based on years, the 365 would be used to convert that.

2 Likes

Let’s keep it simple!

SELECT birthday, strftime(’%Y’, ‘now’) - strftime(’%Y’, birthday) as age
FROM users

I subtracted today (‘now’) from the birthday column. strftime takes a string (TEXT: birthday) and returns a date in the first function (’%Y’ being the age in years) to return.

Hi @mega0396910204, welcome to the forums.

I think the reason this generated so much discussion is part of the wording. The bits I’d watch out for here is that the question was how old were users when they signed up (not current age) and the fact that typical human usage always floors age (see comment above about flooring values). People don’t use normal rounding for their age in years which extends even into the legal system in most cases (e.g. you cannot drive a car until you are x years old or more).

1 Like

Hi! I used the following for this same question and received the answer result that I was looking for.
SELECT *,
CASE
WHEN created_at IS NOT NULL
AND birthday IS NOT NULL
THEN (created_at - birthday)
END AS age
FROM users;

Hope this helps. I wanted to use commands from the lesson

Very helpful, thanks!