All right.
So the next problem here the next question is that we're trying to schedule an ad campaign our own internal
campaign that we're going to.
Well not internal but our own campaign where we're trying to get people to sign up new users on our
application.
Let's say we're advertising on Facebook.
We're also considering maybe for a single day we're going to go and put an ad out like on a billboard.
We can't afford a billboard for an entire week.
So we're just going to do one day.
Yeah.
It's kind of hypothetical.
But still let's say that's what we're working with.
And you could expand that obviously to wherever the budget is and whatever your constraints are.
But for us low budget and we're just going to run an ad for a day.
We want to figure out what day we should do that on what day the week is going to work the best.
This is something that is definitely relevant even though it's simplistic here figuring out you know
days of the week or time of the day just as a photographer that's something that is useful that people
are always trying to figure out is what's the best time to post something on Instagram.
What's the worst time what day of the week is the best.
Do you wait you for what time zone do you try and post according to do you post something so that people
in Europe will be able to see it when they're waking up there's this whole science and that's just for
photographers and Instagram who are not even really making any money from it.
So if you started thinking about advertisers then that's where it really matters.
So we're just doing a simple thing to figure out when users register.
But there's all sorts of insights to be garnered about when people are posting photos and how many likes
photos get depending on what time of day they're posted and all that kind of stuff.
But we're working with it so what day of the week do most users register on.
So we only need to work with the users still.
So I'm going to just add a comment.
Most popular registration date so we can recycle part of this query Nexstar start from users.
And we don't really need to order by anything at this point so I can get rid of that.
So what I want to do is use a Group By to collapse these into similar categories but we can't do a group
by created that because unless somebody you know registered at exactly the same time down to the second
on the same day GROUP BY won't work and it will just have you know 100 unique users.
So what we can do is basically extract the day of the week which we've seen how to do that.
So let's start with that let's just use user name comma and do this on separate lines.
Username comma and then we want de name.
This is one way of doing it.
You could also use Format date but Day name.
I happen to remember that one although there are all those other methods I don't remember her function.
I don't remember that I've talked about like the name of a month.
I always forget so sometimes I do end up just you know pulling open the docs and trying to find the
right function.
But de name and then we just pass passen created it.
OK let's try that semi-colon for I forget that.
So now we're seeing the day of the week.
So now all we want to do is group them by those days that week and then count how many people or how
many instances we have for that day of the week.
And then from there we'll just order them to figure out what is most popular.
OK.
So rather than just doing this well at our group by at the end and we don't want to just do group by
like a group I created it like I said we want to do a group by day name created at and what we could
do is just assign an alias here.
So we'll just call this day and then we can just say group by day.
Now if I do this I only get seven results.
Friday Monday Saturday Sunday Thursday Tuesday Wednesday and of course that's not all the data that's
there that's all that's printed out because these groups have been created and we can work with them
behind the scenes underneath the surface.
So what we'll do we can get rid of a username at this point.
We don't care about that.
We're going to just group them by day and then we're going to add our count star just like that.
And let's see what we get.
OK.
So you can see it's a pretty even distribution.
Let's go ahead and sort of do an order by day
but we don't mean day that's ordering alphabetically over here.
We're going to do is order by count start.
So I'll give that an alias as well I'll call it total.
We want to order by total
All right and of course if we wanted to you can change the order
technically the question we're asking is What's the most popular day.
And well we actually have two days that are tied for that Thursday and Sunday.
Now this data is not relevant to the real world.
I have no idea.
Actually I'm pretty sure those days are not the most popular.
Last time I looked into it for my own purposes I believe.
Oh boy I believe it was the middle of the week like Tuesday and Wednesday roughly late afternoon early
evening Pacific time like 5:00 p.m. Pacific Time missed it best time.
I'm not sure why.
But again I didn't create this data to be realistic.
I just tried to come up with something that would be interesting whether or not it actually applies
to the real world's rules.
OK.
So what we could do is you know limit it at the end.
If we just limited to one it gives us Thursday because it comes first we could do limit two as well.
All right.
And this is a good time to mention that for most of these things in this section there isn't only one
way of doing it.
Sometimes there really is but often there are a couple of different ways of approaching things.
And if you feel like you came up with something whether it's just different and you're not sure if it's
better or you feel like you did come up with something that's more efficient or cleaner or that you
prefer.
Absolutely.
Don't hesitate to post that in the discussion for the course and we'll do our best to get back to you
with feedback on it.
But at the very least other students will be able to see it and maybe somebody else came up with the
same thing and they won't feel so alone in the world.
Or maybe you did come up with something better that is actually going to benefit other people to see.
But for now this is how we're doing.
Most popular registration date.
And that answers our question.
Apparently we should be doing our registration campaign to get users to sign up on a Thursday or Sunday.