1
00:00:00,090 --> 00:00:03,030
All right, next problem here, this is a little different.

2
00:00:03,150 --> 00:00:09,480
So as I mentioned earlier, a real problem on Instagram are bot bot accounts that basically exist to

3
00:00:09,480 --> 00:00:15,180
go around and like a bunch of stuff or to comment on people's things but not create their own content.

4
00:00:15,180 --> 00:00:16,260
They're not real people.

5
00:00:16,290 --> 00:00:18,690
Oftentimes they're commenting the exact same thing.

6
00:00:18,690 --> 00:00:24,330
Or like, if I post a photo, I'll notice I get like ten likes immediately, like the next second.

7
00:00:24,330 --> 00:00:29,520
And usually there's not ten people who are happening to like it immediately.

8
00:00:29,520 --> 00:00:34,920
There's some sort of program that's happening behind the scenes where it's looking for new posts and

9
00:00:34,920 --> 00:00:36,120
liking them immediately.

10
00:00:36,270 --> 00:00:39,810
So in our hypothetical Instagram clone, we have the same problem.

11
00:00:39,810 --> 00:00:45,280
So what we want to do is just find all the users who we think could be bot their users who have never

12
00:00:45,300 --> 00:00:48,570
or excuse me, their users who have liked every single photo on the site.

13
00:00:48,720 --> 00:00:52,170
So if we have 257 photos, they've liked all of them.

14
00:00:52,170 --> 00:00:54,570
If we have 800 photos, they've liked all of them.

15
00:00:54,780 --> 00:00:59,340
And I'm going to show you a way of doing it that actually uses something we haven't seen yet, something

16
00:00:59,340 --> 00:00:59,880
new.

17
00:01:00,420 --> 00:01:05,010
So go ahead and give it your own shot and then we'll have a solution.

18
00:01:05,640 --> 00:01:10,410
So the first thing we want to do, we know it's going to be a joint between users and likes.

19
00:01:10,410 --> 00:01:20,070
So we can start with this to select star from users and we'll do an inner join likes.

20
00:01:20,070 --> 00:01:25,470
And the reason we know it's an inner join because we don't care about users who don't have any corresponding

21
00:01:25,470 --> 00:01:27,750
likes or likes that don't have a corresponding user.

22
00:01:27,750 --> 00:01:28,890
Those shouldn't exist.

23
00:01:29,100 --> 00:01:32,490
So we want the inner join, we want the overlap only.

24
00:01:32,490 --> 00:01:40,260
So select star from users inner join likes on users id equals likes dot user ID.

25
00:01:40,470 --> 00:01:41,670
And if we start with that.

26
00:01:43,120 --> 00:01:44,210
It takes a moment.

27
00:01:44,840 --> 00:01:48,410
You've got, remember, 8782 likes.

28
00:01:49,250 --> 00:01:54,470
And now what we want to do is group them based off of the user who did the liking.

29
00:01:54,980 --> 00:01:59,690
So we'll have a group by and we have a bunch of choices of how we group them.

30
00:01:59,870 --> 00:02:05,450
We could do user state ID or we can do like user ID.

31
00:02:06,830 --> 00:02:07,790
We'll save.

32
00:02:09,259 --> 00:02:09,729
Okay.

33
00:02:09,889 --> 00:02:17,240
So now we have 77 rows here and we can see there's a bunch of information.

34
00:02:17,240 --> 00:02:18,710
We've got the username.

35
00:02:19,100 --> 00:02:20,310
We don't need created at.

36
00:02:20,330 --> 00:02:21,720
So let's slim this down a bit.

37
00:02:21,740 --> 00:02:29,120
Let's do select username user ID and we'll just keep it at that.

38
00:02:32,430 --> 00:02:36,960
So these are all the users who have liked anything at all.

39
00:02:37,110 --> 00:02:38,550
We're grouping them, right?

40
00:02:38,550 --> 00:02:42,100
So they're grouped by user ID, missing a comma.

41
00:02:42,120 --> 00:02:43,140
That's what's going on.

42
00:02:45,060 --> 00:02:47,700
Now, what we want to do is figure out how many.

43
00:02:47,940 --> 00:02:52,890
So that's just a matter of using Count Star, which you should feel pretty good about.

44
00:02:52,890 --> 00:02:54,810
Now, we'll give it an alias right now.

45
00:02:58,380 --> 00:03:01,680
And we can get rid of user ID as well and just do a username.

46
00:03:03,570 --> 00:03:04,110
Okay.

47
00:03:04,470 --> 00:03:10,020
So we can see that we have Andre Purdy, 85 has 94 likes.

48
00:03:10,320 --> 00:03:12,960
Let's give this actually numb likes.

49
00:03:13,980 --> 00:03:18,660
But the question was not just to figure out who has the most, but to figure out the people who have

50
00:03:18,660 --> 00:03:20,910
liked every single photo.

51
00:03:21,210 --> 00:03:25,590
And if you don't remember how many we have 257.

52
00:03:26,310 --> 00:03:27,990
But we're not talking about cheating.

53
00:03:28,590 --> 00:03:32,430
We could cheat if we wanted to check exactly 4 to 57.

54
00:03:33,150 --> 00:03:34,200
It's not too bad.

55
00:03:34,200 --> 00:03:41,640
But you might think you could just do like a where where num likes equals two, five, seven, but that

56
00:03:41,640 --> 00:03:42,960
won't actually work.

57
00:03:43,620 --> 00:03:50,850
And that's because where clauses actually go before the group by telling it what the data you'd like

58
00:03:50,850 --> 00:03:56,310
to select two group versus what you'd like to select from the group's data.

59
00:03:56,310 --> 00:03:58,470
So where doesn't work?

60
00:03:58,470 --> 00:04:04,230
We need to use something different, which we haven't seen, which is called having and having X, like

61
00:04:04,230 --> 00:04:06,420
where it's just a different word.

62
00:04:06,750 --> 00:04:13,620
And what it will do is take our groups data, our end result and allow us to filter based off of a clause

63
00:04:13,620 --> 00:04:20,279
so we can say having and we want to say total equals 257 just like that.

64
00:04:22,000 --> 00:04:23,140
So now if we do it.

65
00:04:24,140 --> 00:04:25,000
Oh total.

66
00:04:25,010 --> 00:04:26,930
I changed the name to numb likes.

67
00:04:26,930 --> 00:04:28,580
But now if I do it, you can see.

68
00:04:28,580 --> 00:04:33,740
Okay, these are the people who have liked it 257 times, like every single post.

69
00:04:33,740 --> 00:04:39,470
But the problem again is be hardcoded to 57, so we could fix that very easily.

70
00:04:39,470 --> 00:04:46,520
We want this to be dynamic, however many photos we have and we can just use a subquery to select Count

71
00:04:47,030 --> 00:04:50,720
Star from photos.

72
00:04:50,720 --> 00:04:56,450
So this will be where the number of things they've liked is equal to the total number of our photos.

73
00:04:58,250 --> 00:04:58,910
And there we go.

74
00:04:58,910 --> 00:05:00,110
We get the same exact thing.

75
00:05:00,110 --> 00:05:07,100
But this time if I add 1000 photos or remove ten photos, this will still always be the total number

76
00:05:07,100 --> 00:05:07,820
of photos.

77
00:05:08,300 --> 00:05:08,710
Okay.

78
00:05:08,910 --> 00:05:13,850
Kind of an ugly query with a subquery here, but if you came up with something different, post it.

79
00:05:13,850 --> 00:05:14,750
We'll have a discussion.