Okay.
So next up, suppose getting the brains of our Instagram clone application employees and we make money
by having sponsored content, by having advertisers post things on our app and they want to know what
hashtags they should use, which are most popular at any given point.
So what we want to do is just find the five most commonly used hashtags so we can tell our advertisers,
These are the hashtags you should use on your own stuff because they're trending right now.
So what people are looking for.
So to do it, it's pretty straightforward.
We have a single join.
We're basically going to connect hashtags or tags with photo tags.
Remember, we have those two tables.
The one table is just the name of the tag and the other table is every time the tag is used and applied
to a photo.
So we want to count.
Basically, we're on a group, all of those photo tags together, count them, but then we want to find
the associated name of the tag.
So let's start that.
We'll do six most popular hashtags, five most popular hashtags.
So if we start with just selecting start from tags as we've seen before, it's just a list of the hashtags
themselves and then, well, an ID, a hash tag name and then create a that.
But we've also got photo underscore tags and we've got a bunch of those 500.
And each one is just a hash tag or a tag ID and then a photo ID that it's being applied to see if we
can get to the top here.
Here we go.
So the photo ID, photo ID 14 is being tagged with whatever has tag ID one, which is sunset.
So photo 14 has the tag sunset.
So what we want to do is we can start if we wanted by just focusing on these and counting how many times
each tag was used.
But it makes it a little easier to follow if we join tags.
So we have a name of the tag as well.
So we'll do that.
We'll do select start from photo tags.
And then we'll do join tags.
Get rid of that other stuff here on photo tags.
Dot.
Tag id.
Which is this where this tag id equals tags?
Dot id.
So now what we see, there's a lot of stuff.
500 rose.
If we go up to the top.
We can see they're already being grouped roughly.
Not exactly, but they're being grouped roughly by the tag name.
So we've got 14 was tagged with Sunset, 21 was tagged with Sunset for 45 was tagged sunset and so on.
So then what we want to do is basically collapse them using group by and we could use a tag name to
collapse them, but we could also use the tag ID, which is easier because it's just a simple integer
rather than having to work with strings and text.
So we'll do the tag ID.
So that's group by tags ID.
Now we do this, we're getting closer.
Now we only have 21 because if in group together then finally rather than selecting star, lets select
the tag tags dot tag name comma do these on separate lines.
We want the count star.
Okay.
So we can see things like Sunset was used 19 times, photography 16 times, Sunrise 17 times.
Then we'll go and give this an alias as well.
Just call it as what's a good one total.
Okay.
Next up, we want to find the maximum so you could use Max but would be better.
What I'm going to do is just use order by and then limit because we want the five most popular.
So actually you couldn't use Max because we want five.
So we'll do order by what are we ordering by this count here, which we call total order by total.
And if we do that it will be an in ascending order.
So we want to change that and then we want to limit it to five and that should do the trick.
And there we go.
We see that the top five tags right now, number one by far is smile.
Apparently it's very popular and we've got beach at 42 party 39 fun and lol and I know for smile and
beach I kind of engineered to be there because when I was making the data I wanted some hashtags.
It can be used in multiple contexts and things like Beach could be used in a landscape photography shot
alongside sunrise or sunset or landscape, but it could also be used like in a model photography shot
alongside model or beautiful.
It could be used in a party, you know, alongside a party hashtag.
If it was a beach party, smile can be used in a lot of ways, so can party.
So anyways, I tried to come up with certain tags that could be used in different ways and there we
go.
Those are our top five hashtags at the moment.
Next up, you guessed it, something else.