All right, next problem here, this is a little different.
So as I mentioned earlier, a real problem on Instagram are bot bot accounts that basically exist to
go around and like a bunch of stuff or to comment on people's things but not create their own content.
They're not real people.
Oftentimes they're commenting the exact same thing.
Or like, if I post a photo, I'll notice I get like ten likes immediately, like the next second.
And usually there's not ten people who are happening to like it immediately.
There's some sort of program that's happening behind the scenes where it's looking for new posts and
liking them immediately.
So in our hypothetical Instagram clone, we have the same problem.
So what we want to do is just find all the users who we think could be bot their users who have never
or excuse me, their users who have liked every single photo on the site.
So if we have 257 photos, they've liked all of them.
If we have 800 photos, they've liked all of them.
And I'm going to show you a way of doing it that actually uses something we haven't seen yet, something
new.
So go ahead and give it your own shot and then we'll have a solution.
So the first thing we want to do, we know it's going to be a joint between users and likes.
So we can start with this to select star from users and we'll do an inner join likes.
And the reason we know it's an inner join because we don't care about users who don't have any corresponding
likes or likes that don't have a corresponding user.
Those shouldn't exist.
So we want the inner join, we want the overlap only.
So select star from users inner join likes on users id equals likes dot user ID.
And if we start with that.
It takes a moment.
You've got, remember, 8782 likes.
And now what we want to do is group them based off of the user who did the liking.
So we'll have a group by and we have a bunch of choices of how we group them.
We could do user state ID or we can do like user ID.
We'll save.
Okay.
So now we have 77 rows here and we can see there's a bunch of information.
We've got the username.
We don't need created at.
So let's slim this down a bit.
Let's do select username user ID and we'll just keep it at that.
So these are all the users who have liked anything at all.
We're grouping them, right?
So they're grouped by user ID, missing a comma.
That's what's going on.
Now, what we want to do is figure out how many.
So that's just a matter of using Count Star, which you should feel pretty good about.
Now, we'll give it an alias right now.
And we can get rid of user ID as well and just do a username.
Okay.
So we can see that we have Andre Purdy, 85 has 94 likes.
Let's give this actually numb likes.
But the question was not just to figure out who has the most, but to figure out the people who have
liked every single photo.
And if you don't remember how many we have 257.
But we're not talking about cheating.
We could cheat if we wanted to check exactly 4 to 57.
It's not too bad.
But you might think you could just do like a where where num likes equals two, five, seven, but that
won't actually work.
And that's because where clauses actually go before the group by telling it what the data you'd like
to select two group versus what you'd like to select from the group's data.
So where doesn't work?
We need to use something different, which we haven't seen, which is called having and having X, like
where it's just a different word.
And what it will do is take our groups data, our end result and allow us to filter based off of a clause
so we can say having and we want to say total equals 257 just like that.
So now if we do it.
Oh total.
I changed the name to numb likes.
But now if I do it, you can see.
Okay, these are the people who have liked it 257 times, like every single post.
But the problem again is be hardcoded to 57, so we could fix that very easily.
We want this to be dynamic, however many photos we have and we can just use a subquery to select Count
Star from photos.
So this will be where the number of things they've liked is equal to the total number of our photos.
And there we go.
We get the same exact thing.
But this time if I add 1000 photos or remove ten photos, this will still always be the total number
of photos.
Okay.
Kind of an ugly query with a subquery here, but if you came up with something different, post it.
We'll have a discussion.