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. 
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).
1 Like
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.
10 Likes
Oh! Thanks for pointing that out. Sometimes I make my own problems 
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.
1 Like
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.
2 Likes
This line makes the most sense and its the shortest 
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 
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
2 Likes
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
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?
2 Likes
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).
2 Likes
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
2 Likes
Very helpful, thanks!
1 Like