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 whatever 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 of the week is going to work the best.
This is something that is definitely relevant, even though it's simplistic here.
We're figuring out 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 know, 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 start thinking about advertisers, then that's where it really matters.
So we're just doing this 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 this, so what day of the week do most users register on?
So we only need to work with users still.
So I'm going to just add a comment.
Most popular registration date so we can recycle part of this query select star from users and we don't
really need to order buy anything at this point so I can get rid of that.
So what we want to do is use a group by to collapse these into similar categories.
But we can't do a group by creating that because unless somebody registered at exactly the same time
down to the second on the same day, Group Buy won't work and we'll just have 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 do select username comma and I'll 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 that are functions.
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 pulling open the docs and trying to find the right function.
But day name.
And then we just passed in.
Created at.
Okay.
Let's try that o semicolon before 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 of the 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 day is most popular.
Okay.
So rather than just doing this, we'll add our group by at the end.
And we don't want to just do group by like group I created at 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 or underneath the surface.
So what we'll do, we can get rid of 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.
Och.
So you can see it's a pretty even distribution.
Let's go ahead and sort so we'll do an order by day.
Oops.
We don't mean day that's ordering alphabetically over here.
What we want to do is order by Count Star.
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, 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 p.m. Pacific time is the 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.
Okay, so what we could do is limit it at the end if we just limited it to one.
It gives us Thursday because it comes first.
We could do a limit too 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 we're 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 a Sunday.