Get here we go.
So let's first start off by just making a new file to work in.
So we have some cleaning.
We'll just call it exercises as well.
So we have a nice place to put the answers.
OK.
So our first one here find the earliest date a user joined.
And we're not selecting anything except the earliest date.
So all we need to do is take advantage of created that and use the men operator them in function.
So it's just a matter of select men created at from users.
And we'll see if that
copy and paste it and the one that I get here looks like this.
So 2016 5 one notice says earliest date so that's the easier part to fix.
And then notice the format.
So let's do as earliest date first.
That will make it cleaner then we need to format the date.
So I'm going to put this on a new line and the way that we format remember is we use date underscore
format.
Think back to our date section.
So we're formatting the result of the minimum created which is this.
And we have a second argument which is the date string how we want to format it.
And the one I gave you had the month name which I believe is capital M and then that day with the suffix
which is I believe capital D and the year four letter for numbers capital Y.
So you need percent capital D percent capital no backwards capital M percent capital D percent capital
Y.
Let's see oh I have to copy this pasted in.
And there we go.
May 1st 2016.
OK.
Look we're only a day off from the first dataset that I added when I made these exercises I had a different
set of data and you just watch me rerun it delete it or create 500 new ones so these will be slightly
different than my answers to.
Next find the email of that user.
So the same one that we just found this is the earliest date.
How do we find that e-mail.
So let me label this challenge one and this one.
We have to use a subquery because basically what we're trying to do is well first we can clean this
up a bit.
We don't need to format this one but basically what we're trying to do is take this earliest date which
looks like this.
And then we want to do a select well do e-mail from users where created at is equal to that date.
How do we find that user.
So let's just do it manually.
It looks like our answer is Cole Bailey at yahoo dot com so we can use a so Kery to help us where all
we have to do we can get rid of this and say select email Commack created at Or we could just say select
star to make it shorter from users where created it equals and then we have to do our several query
select men created out from users.
So those parentheses indicate that this is a query that needs to happen.
So this is evaluated what is men created that need my parentheses to work there go.
What is the miscreated.
Well it's going to be this.
So then basically this whole thing is replaced by that and then we're finding users start from users
where creativity is equal to that.
So let's try it.
There we go.
Looks good.
We've got e-mail and created at of course it looks different but this is the correct answer.
Call Baly and we saw that because earlier we just manually selected it based off of that date and it
worked perfect.
Moving on.
So we're taking every user taking they're created at date and then we're extracting the month name from
that date whatever the month is combining them.
We're grouping them and counting how many exist.
So how many users signed up in November.
In our case November was the highest and then we're.
Us well.
So let's start by just extracting the month name.
So if we were to take a date like where's that.
This one right here.
How do we get the month name from that.
Because if we just do select the month of that it gives us a number so that we can do instead is month
name.
And that gives us may.
So we'll start with that.
Well do select month name of created at from users
and we get 500 different month names.
Well there's only 12 different ones but 500 rows.
So then what we want to do is a simple group by and let's just give it a name like as month and grouped
by month instead.
So rather than retyping that whole thing.
OK so now we have 12 assuming that we have a date in every month of the year which we probably do if
we have 500 but we don't see how many there are.
So then we need our second field and our second field is count we just do count start just like that.
And here we go almost there all that we need to do.
I believe I gave this an alias of count and then we also want to order by count and we want it to be
descending.
Let's see if it works.
Riego November the highest count as well with 52.
Although it diverges.
January was first or was second.
In my case may and then April.
All right.
So we use this month name and we group them based off the third month.
You could also just replace it with this.
But it's easier just to type a month and then grouping them counting them and ordering them.
I mean just put a note that this is challenge 3.
Moving on.
Challenge for count the number of users with Yahoo emails.
So for this one it's a bit of a throwback and we need to do is use like to figure out you know which
emails have Yahoo in them.
So we'll do a challenge for let's do a select star from users where we just need to say we're created
at like.
And we don't just want to say Yahoo.
Remember that's only going to find in one of my saying created an email like Yahoo.
We don't want to do that because that's only where email is exactly Yahoo.
So we need these placeholders but we could improve this a bit because this would match anything that
had Yahoo in the string anywhere and we could have someone's email be you know Yahoo.
Tim at gmail dot com.
Or maybe someones last name is Yahoo or something Yahoo's.
So we don't want that.
So all we want to change is we'll do at Yahoo dot com and this will guarantee that the string ends at
yahoo dot com.
So now let's take a look.
Here we go these are all yahoo dot com emails and then all we wanted to do is count how many.
So rather than selecting star who would you select count Starr as Yahoo users.
I think it's what we call it from users where email lists like Yahoo.
And this time there is 170 and finally challenge and refine calculate the total number of users for
each email host.
So this one involves using like along with a case statement it's a little bit complicated.
So we're doing the same thing we're recounting number of users but we're doing it dependent on the provider.
So we're going to have a case statement so we'll just start right off with select we'll do case and
I like to do my end as well.
I think we called it provider.
Yep.
So we'll start with that and will do from users and what is our case.
Well we can start with this Yahoo.
One will say when email is like Yahoo then provider is Yahoo.
Right.
So we'll will do the same thing just basically copy this and say when email is like at gmail dot com
and in Gmail then gmail one thing I should note there is this kind of nicer way of doing this if you
are familiar with regular expressions but thats out of the scope of this course.
But if you do know it you can get by with that.
So we've also got Hotmail
than Hotmail.
There we go.
OK.
Then finally we'll have or else which will just be other.
So we have this provider field now a column that will have gmail yahoo hotmail or other.
So let's do e-mail Khama case so that we can see the email side by side against what we pulled out of
it.
We got 500.
Take this one re-adapt more one at gmail.
We get gmail.
Ruben Medhurst at Yahoo.
We get Yahoo.
Perfect.
So now just a matter of grouping them together and counting.
So we can get rid of e-mail.
We don't need to display it then we're just going to group by provider which is what we just created.
So let's start with that.
Cool.
So we're grouping them together.
Looks like we don't have any others in this case.
We're going to group them.
And then finally we're going to count after our case statement ends we added comma count star
copy paste.
And there's just a small discrepancy which is this is called total users.
Also this is sorted.
Very easy to change.
Call it has total users like to capitalize that actually.
And then finally well let's just make sure that that works to start.
Great.
Now we need to sort of order by total users and if we do it this way you'll see that it's ascending.
We want to do the sending.
Right now we've got all the providers on the left and their total number of users.
Perfect.
All right.
So hopefully enjoyed that chance of working with more data though isn't hard coded in some of its dynamic
all of its namak 500 plus users.
Next up in this next section we're finally going to connect the database we just created which is filled
with all that see data and we're going to connect it with a web application and figure out how to get
those to talk.