OK so next up you know suppose get in 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 and 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 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 two tables that 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 a group of those photo tags together count them but then we want to
find the associated name of the tag.
So let's start that will do six most popular hashtags five most popular hashtags.
So if we start with just selecting star tags as we've seen before it's just a list of the hashtags themselves.
And then well NRD hash tag name and then created it.
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 I.D. 14 is being tagged with whatever hash tag I.D. one wishes sunset.
So photo 14 has a tag sunset.
So what we want to do is we can start if we want and 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 star from photo tags and then we'll do join tags.
Get rid of the other stuff here on photo tags dot tag ID.
So is this where this tag I.D. equals tags dot I.D. So now we see there's a lot of stuff five hundred
rows if we go up to the top we can either already being grouped roughly.
Not exactly but they're being grouped roughly by the tag name.
So we've got you know 14 was tagged with sunset 21 or Taghreed sunset 40 or 45 was tagged to 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 that ID.
So that group by tags that Id Now we do this.
We're getting closer.
Now we only have 21 because they've been grouped together.
Then finally rather than selecting star let's select the tag tags that tag name comma.
Do these on separate lines.
We want the count Star
OK so we can see things like sunset was used 19 times photography 16 times sunrise 17 times.
Then we'll go and give an alias as well just call it as well to get total.
Next up we want to find the maximum.
So you could use max but would be better.
But I'm going to do is just use ORDER BY and limit because we want the five most popular so actually
you couldn't use Max because we want 5.
So we'll do order by what are we ordering by this count here which we called Total Order by total.
And if we do that it will be in in ascending order.
So we want to change that.
And then we want to limit it to 5 and that should do the trick.
And there we go.
We see that the top five tagged right now number one by far is smile.
Apparently it's very popular and we've got beach at 42 party thirty nine fun and EHLO well.
And I know for you know smile and beach are kind of engineered to be there because when I was making
the data I wanted some hash tags that could be used in multiple contexts and things like you know Beach
could be used in landscape photography shot alongside sunrise or sunset or landscape but it can also
be used like in a model photography shot alongside model beautiful.
It could be used in a party you know alongside a party hash tag 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.