Okay.
Here we go.
So let's first start off by just making a new file to work in.
So we have some clean.
We'll just call it exercises dot SQL, just so we have a nice place to put the answers.
Okay, so our first one here, find the earliest date a user joint and we're not selecting anything
except the earliest date.
So all we need to do is take advantage of created at and use the min operator the min function.
So it's just a matter of select min created it from users and we'll save that.
Copy it, paste it.
And the one that I get here looks like this.
So, 2016 501.
Notice it says earliest date.
So that's 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 the day with the suffix,
which is, I believe Capital DX and then the year four letter or four numbers, capital Y.
So you need percent capital D, percent capital M note 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.
Okay, look, we're only a day off from the first data set that I added when I made these exercises,
I had a different set of data, and you just watch me, rerun it, delete it all, 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 the email?
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 we'll do an email from users where created it 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.com.
So we can use a subquery to help us where all we have to do.
We can get rid of this and say select email comma created.
Or we could just say select star to make it shorter from users where created it equals.
And then we have to do our subquery.
Select min created debt from users.
So those parentheses indicate that this is a query that needs to happen.
So this is evaluated.
What is min created at?
I need my parentheses to work there.
There we go.
What is the min created at?
Well, it's going to be this.
So then basically this whole thing is replaced by that.
And then we're finding users, staff from users where created is equal to that.
So let's try it.
There we go.
Looks good.
We've got email and created that.
Of course it looks different, but this is the correct answer called Bailey.
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 their created at date and then we're extracting the month name from
that date, whatever the month is.
Combining them all, 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 sorting as well.
So let's start by just extracting the month name.
So if we were to take a date like.
Where is that?
This one right here.
How do we get the month name from that?
Because if we just do a select month of that, it gives us a number.
So what we can do instead is month name and that gives us may.
So we'll start with that.
We'll do select month name of created ET from users.
And we get 500 different month names.
Well, there's only 12 different ones, but 500 rose.
So then what we want to do is a simple group buy and let's just give it a name like as month and group
by month instead.
Rather than retyping that whole thing.
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 star.
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.
Here we go.
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 of that month.
You could also just replace it with this, but it's easier just to type a month and then we're grouping
them, counting them and ordering them.
Let me just put a note that this is challenge three, moving on, challenge for count the number of
users with Yahoo!
Emails.
So for this one, it's a bit of a throwback.
All we need to do is use like to to figure out 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 and what am I saying created it where email like Yahoo we don't want
to do that because that's only where email is exactly.
Yahoo!
So we need these the 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 Yahoo!
Tim at gmail.com or maybe someone's 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.com and this will guarantee that the string ends in yahoo.com.
So now let's take a look.
Here we go.
These are all Yahoo.com emails.
And then all we wanted to do is count how many.
So rather than selecting Star, we'll do select Count Star as Yahoo users I think is what we called
it from users where email is like Yahoo!
And this time there's 170.
And finally, challenge number five, 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 where we're counting the 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 I think we called it, provider.
Yep.
So we'll start with that and we'll 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 do the same thing.
Just basically copy this.
And say one email is like at gmail.com.
I'm going to end in Gmail, then Gmail.
One thing I should note, there is a kind of nicer way of doing this if you are familiar with regular
expressions, but that's 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.
Then Hotmail.
There we go.
Then finally we'll have our else, which will just be other.
So we have this provider field now, a column that will have Gmail, Yahoo and Hotmail or other.
So let's do email, comma, case so that we can see the email side by side against what we pulled out
of it.
So we've got 500.
Let's take this one ray dot more one.
At Gmail we get Gmail.
Rubin Medhurst at Yahoo!
We get Yahoo!
Perfect.
So now it's just a matter of grouping them together and counting so we can get rid of email.
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 add a 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 as total users.
I'd like to capitalize that, actually.
And then finally.
Well, let's just make sure that that works to start.
Great.
And now we need to sort it order by total users.
And if we do it this way.
You'll see that it's ascending.
We want to do descending.
All right, now we've got all the providers on the left and their total number of users.
Perfect.
All right, so hopefully you enjoyed that chance of working with more data that isn't hardcoded in some
of its dynamic, all of its dynamic, 500 plus users.
Next up in the next section, we're finally going to connect the database we just created.
We just filled with all that seed data, and we're going to connect it with a web application and figure
out how to get those to talk.