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 are commenting the exact same thing.
Or like if I post a photo I'll notice I get like 10 likes immediately like the next second.
And usually there's not 10 people who are happening to you know 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 phone we have the same problem.
So what we want to do is just find all the users who we think could be bought 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 liked all of them.
If we have 800 photos if I liked all of them and I'm going to show you a way of doing it that actually
use is something we haven't seen yet.
Something new.
So go ahead give it your own shot and then we'll have a solution.
OK.
So the first thing we want to do we know it's going to be a join tween users and like so we can start
with this to select a star from users and we'll do an INNER JOIN like this.
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 or don't have a corresponding user that shouldn't exist.
So we want the INNER JOIN on the overlap only so select start from users or join likes on users start
ID calls like Dot use your ID.
And if we start with that takes a moment you've got remember eight 8000 782 likes.
And now what we want to do is group them based off of the user who did the liking.
So we have a group by.
And we have a bunch of choices of how we group them.
We could do user ID or we can do like user ID will save.
OK so now we have 77 rows here and we can see you know there's a bunch of information we've got the
username we don't need created at.
So let's them this down a bit let's do select user name User ID and we'll just keep it at that.
OK.
So these are all the users who have liked anything at all.
We're grouping them.
Right.
So the groups 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.
OK and we can get rid of user ID as well and just to use your name.
OK so we can see that you know we have Andre Purdey 85 has 94 likes.
Let's give this actually numb like this.
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 you know if you don't remember how many we have 257 we're not talking about cheating.
We could cheat if we wanted to check.
Exactly for 257.
It's not too bad but you might think you could just do like a Where where.
Numb like equals to five 7.
But that won't actually work.
And that's because where clauses actually go before the group by telling it what the data you like to
select to 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 group's 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 up total I change the name to numb like.
But now if I do it can see OK these are the people who have liked it.
257 times like every single post.
But the problem again is hard coded 2: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 it likes is equal to the total number of our photos and every
go we get the same exact thing.
But this time if I add a thousand photos or remove 10 photos this will still always be the total number
of photos.
OK.
Kind of an ugly query with a subquery here but if you came up with something different.
Post it.
We'll have a discussion.