All righty.
Welcome back to our next riveting installment of asking random questions about our database data that
we inserted.
Yeah, that's that's what we're doing here.
So our next question here is basically I'll start 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 back story 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 ties them together?
We're doing just one, and we're not just trying to find the photo itself, but we all know who posted
it, who is responsible for it, so we can send them whatever their prize is, or 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, meaning the most number
of likes, and then we can worry about the username and the user afterwards.
So we'll do four identify most popular photo and user who created it.
So to do that, it's going to be a select we'll do select star to start from photos.
And if we just start there and we have what is it, 257 but no information about likes because that's
a separate table.
Of course, if we do our select star from likes.
We have 8782 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 liker, and then we have the ID of the 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 matching the photos with the likes that correspond to them.
So that's going to be a join and it will just be an inner join likes on and we'll do where like dot
photo ID equals photos dot ID.
So this is going to be a big table here.
Oh, what's my error here?
Extra semicolon.
Try that again.
I'll take a moment.
We've got 8782 rows, but just like we did with likes.
But this time, the rows 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 let's see, photos, ID and photos, dot, image, URL, comma, and then what do
we have on the likes table?
If you remember, if you go over to our schema here, likes is very simple.
We just have our user ID and our photo ID, so we'll just do what we can do both.
Let's just do like user ID to start.
And you can see.
All right.
So this is all on our last photo with ID of 257.
All of these are likes for that photo and they are all by this user or by different users.
User D three, five, ten, 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 the likes table.
Now, what we want to do is figure out how many each photo has.
So we're going to need to do a group by.
And before I do that, let's just spaced this out nicely and we're going to group by.
We could do the image URL, but it's much easier just to do it with the integer of the photo ID.
So group by photos up here dot id.
Now if we do this.
We have 257 rows now because we only have 257 photos.
And then over here, this is still just displaying the first ID of the user who liked 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 state ID.
And now you can see over here you've got photos that vary.
Some of them have a bunch of like some of them not so many.
Now, all we want to do is find the number one.
We could use maximum or max.
I'm just going to do a limit 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.
I'll call it total order by total and by default that will be ascending, which is not what we want.
Right.
We want the opposite
getting their closer.
Then finally, what we want to do is limit it to one and move our semicolon.
So that gives us the name or excuse me, the ID and the image URL 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 subquery 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 inner join this time on users and we're trying to connect it where
the not the like join.
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.
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 username.
So now if we run this.
You can see we get Zack Kemmerer 93 with ID of 145 is a person who is responsible for our most popular
photo of all time.
This photo Jarrett name is our Earl with a total of 48 likes.