All righty.
Welcome back to our next riveting installment of asking random questions about our data base data that
we inserted Yeah that's what we're doing here.
So our next question here is basically started the other way around for once.
What we're trying to do is figure out what is the single most liked photo in our database.
And I actually don't know if Instagram has done this before.
I know third parties have like people have used Instagram API to try and find the most popular Instagram
of all time.
That's essentially what we're trying to do.
Let's say we're running a contest is the backstory I came up with but this is something that just might
be nice to know internally at a company like Instagram who is responsible for the most liked photo of
all time or the top ten most liked.
Is there anything in common or even the top 1000.
What you know tied some together.
We're doing just one and we're not just trying to find the photo itself but we don't know who posted
it who is responsible for it.
So we can send them whatever their prizes or you know we can mention them in a blog post or something.
So the first thing we need to do is just find the most popular photo to start meeting the most number
of likes.
And then we can worry about the username and the user afterwards.
So we'll do for identify.
Most popular photo and user who created it.
OK.
So to do that can be a select will do start to start from photos.
And if we just start there you know we have 257 but no information about likes because that's a separate
table of course.
You are like start from like
we have eight thousand seven hundred eighty two likes and they're not all displayed here for us to see.
But we've got two fields.
We have the ID of the person who is liking the Leiker and then we have the idea for a photo that is
being liked.
So what we want to work with is the photo ID we don't care about who is doing the liking.
We care about you know matching the photos with the likes that correspond to them.
So that's going to be adjoin and it will just be an inner join likes on and we'll do where like start
photo ID equals photos dot ID.
So this is going to be you know a big table here.
Oh what's my air here.
Extra semi-colon.
Try that again.
Well take a moment.
We've got you know eight thousand seven hundred eighty two rows.
But just like we did with Mike Smith This time the roads are significantly larger.
We have a bunch of data we don't need most of that.
So rather than just selecting star which gives us too much information let's whittle it down a bit and
we'll just do it see photos ID and photos died.
Image you are.
Calm And then what do we have on the likes table.
If you remember to go over to our schema here likes it's very simple.
We just have our user ID and our photo ID.
So we'll just do what we can do both but let's just do like user ID to start
and you can see.
All right.
So this is all you know on our last photo ID of 257.
All of these are likes for that photo and they are all you know by this user or by different users.
These are already three five 10 12 14 15.
We don't need that bit of information.
I just want to show you just so that you can tell we're getting something from it like stable.
Now what we want to do is figure out how many each photo has.
So we're going to need to do a group buy.
And before I do that let's just spaced this out nicely.
OK.
And we're going to get by.
We could do the image you Earl but it's much easier just to do it with the integer of the photo ID so
group by photos up here Heidi.
Now if we do this we have 257 Rosenau because we only have 257 photos and then over here this is still
just displaying the first ID of the user who like that photo.
So that's not relevant.
What we actually want to do is display the total number of users who liked it and that's just a matter
of a simple count.
Now that we've grouped them by photo ID
and now you can see over here you know we've got photos that vary.
Some of them have a bunch of like some of them not so many.
Now we want to do is find the number one we could use maximum Max.
I'm just going to do a limited one for that to work we need to do an order by.
So let's actually start with the order by and we want to order by count.
So I'll give it an alias or call it total order by total and by default that would be sending which
is not what we want it we want the opposite
getting there.
OK.
Closer than Finally what we want to do is limit it to one and move our semi colon.
OK.
So that gives us the name excuse me the ID image you are out and the total number of likes for the photo
of the single photo that has the most likes.
Which for us is 48.
And this is the image of the photo.
Now we want to figure out who it belongs to who created it.
So we could do a separate query.
We could even do a sub query if you wanted to but the easiest way is to do another join.
So I'll go ahead and show that join.
Basically all we need to do is get the user information in there as well.
And so we're just going to do another and are joined this time on users and we're trying to connect
it where the not the like joint excuse me not the likes dot user ID.
That's the idea of the person who liked it.
We don't care about that.
We want the photos dot user ID equals user ID.
And now you're not going to notice anything right away because we're not displaying it but we are joining
all the user information and all that we really want is user name.
Now if we run this can see if we get Zach Kemmer 93 which idea of 145 is a person who is responsible
for our most popular photo of all time.
This photo.
Jarrett that name is or you are all with a total of 48 likes.