1 00:00:00,330 --> 00:00:01,050 All righty. 2 00:00:01,080 --> 00:00:07,710 Welcome back to our next riveting installment of asking random questions about our database data that 3 00:00:07,710 --> 00:00:08,640 we inserted. 4 00:00:09,350 --> 00:00:11,380 Yeah, that's that's what we're doing here. 5 00:00:11,390 --> 00:00:17,390 So our next question here is basically I'll start the other way around for once. 6 00:00:17,390 --> 00:00:22,310 What we're trying to do is figure out what is the single most liked photo in our database. 7 00:00:22,430 --> 00:00:25,790 And I actually don't know if Instagram has done this before. 8 00:00:25,790 --> 00:00:32,060 I know third parties have like people have used Instagram API to try and find the most popular Instagram 9 00:00:32,060 --> 00:00:32,960 of all time. 10 00:00:33,470 --> 00:00:35,180 That's essentially what we're trying to do. 11 00:00:35,210 --> 00:00:40,490 Let's say we're running a contest is the back story I came up with, but this is something that just 12 00:00:40,490 --> 00:00:45,500 might be nice to know internally at a company like Instagram, who is responsible for the most liked 13 00:00:45,500 --> 00:00:47,570 photo of all time or the top ten most liked? 14 00:00:47,570 --> 00:00:50,090 Is there anything in common or even the top 1000? 15 00:00:50,090 --> 00:00:51,920 What ties them together? 16 00:00:52,130 --> 00:00:58,400 We're doing just one, and we're not just trying to find the photo itself, but we all know who posted 17 00:00:58,400 --> 00:01:03,470 it, who is responsible for it, so we can send them whatever their prize is, or we can mention them 18 00:01:03,470 --> 00:01:04,849 in a blog post or something. 19 00:01:05,060 --> 00:01:10,820 So the first thing we need to do is just find the most popular photo to start, meaning the most number 20 00:01:10,820 --> 00:01:14,720 of likes, and then we can worry about the username and the user afterwards. 21 00:01:15,590 --> 00:01:26,510 So we'll do four identify most popular photo and user who created it. 22 00:01:27,650 --> 00:01:35,030 So to do that, it's going to be a select we'll do select star to start from photos. 23 00:01:35,660 --> 00:01:42,680 And if we just start there and we have what is it, 257 but no information about likes because that's 24 00:01:42,680 --> 00:01:43,370 a separate table. 25 00:01:43,370 --> 00:01:46,940 Of course, if we do our select star from likes. 26 00:01:50,190 --> 00:01:56,310 We have 8782 likes and they're not all displayed here for us to see. 27 00:01:56,310 --> 00:01:58,100 But we've got two fields. 28 00:01:58,110 --> 00:02:05,970 We have the ID of the person who is liking the liker, and then we have the ID of the photo that is 29 00:02:05,970 --> 00:02:06,860 being liked. 30 00:02:06,870 --> 00:02:09,479 So what we want to work with is the photo ID. 31 00:02:09,479 --> 00:02:11,430 We don't care about who is doing the liking. 32 00:02:11,430 --> 00:02:16,860 We care about matching the photos with the likes that correspond to them. 33 00:02:16,860 --> 00:02:27,780 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 34 00:02:27,780 --> 00:02:32,070 photo ID equals photos dot ID. 35 00:02:33,300 --> 00:02:36,810 So this is going to be a big table here. 36 00:02:36,930 --> 00:02:38,340 Oh, what's my error here? 37 00:02:38,370 --> 00:02:39,600 Extra semicolon. 38 00:02:40,680 --> 00:02:41,550 Try that again. 39 00:02:42,490 --> 00:02:43,450 I'll take a moment. 40 00:02:43,570 --> 00:02:47,740 We've got 8782 rows, but just like we did with likes. 41 00:02:47,740 --> 00:02:50,590 But this time, the rows are significantly larger. 42 00:02:50,620 --> 00:02:51,850 We have a bunch of data. 43 00:02:52,000 --> 00:02:53,580 We don't need most of that. 44 00:02:53,590 --> 00:02:59,560 So rather than just selecting Star, which gives us too much information, let's whittle it down a bit 45 00:02:59,560 --> 00:03:09,190 and we'll just do let's see, photos, ID and photos, dot, image, URL, comma, and then what do 46 00:03:09,190 --> 00:03:11,320 we have on the likes table? 47 00:03:11,320 --> 00:03:15,820 If you remember, if you go over to our schema here, likes is very simple. 48 00:03:15,820 --> 00:03:20,650 We just have our user ID and our photo ID, so we'll just do what we can do both. 49 00:03:20,830 --> 00:03:23,350 Let's just do like user ID to start. 50 00:03:26,610 --> 00:03:27,660 And you can see. 51 00:03:27,660 --> 00:03:28,050 All right. 52 00:03:28,050 --> 00:03:32,510 So this is all on our last photo with ID of 257. 53 00:03:32,520 --> 00:03:40,710 All of these are likes for that photo and they are all by this user or by different users. 54 00:03:40,740 --> 00:03:43,470 User D three, five, ten, 12, 14, 15. 55 00:03:43,470 --> 00:03:45,060 We don't need that bit of information. 56 00:03:45,060 --> 00:03:49,650 I just want to show you just so that you can tell we're getting something from the likes table. 57 00:03:50,070 --> 00:03:54,240 Now, what we want to do is figure out how many each photo has. 58 00:03:54,300 --> 00:03:56,460 So we're going to need to do a group by. 59 00:03:57,930 --> 00:04:05,130 And before I do that, let's just spaced this out nicely and we're going to group by. 60 00:04:05,340 --> 00:04:12,480 We could do the image URL, but it's much easier just to do it with the integer of the photo ID. 61 00:04:12,510 --> 00:04:16,560 So group by photos up here dot id. 62 00:04:17,279 --> 00:04:18,690 Now if we do this. 63 00:04:20,640 --> 00:04:25,710 We have 257 rows now because we only have 257 photos. 64 00:04:26,490 --> 00:04:34,710 And then over here, this is still just displaying the first ID of the user who liked that photo. 65 00:04:34,710 --> 00:04:36,010 So that's not relevant. 66 00:04:36,030 --> 00:04:42,720 What we actually want to do is display the total number of users who liked it, and that's just a matter 67 00:04:42,720 --> 00:04:46,470 of a simple count now that we've grouped them by photo state ID. 68 00:04:49,100 --> 00:04:52,970 And now you can see over here you've got photos that vary. 69 00:04:53,450 --> 00:04:55,910 Some of them have a bunch of like some of them not so many. 70 00:04:56,300 --> 00:04:59,510 Now, all we want to do is find the number one. 71 00:04:59,660 --> 00:05:01,920 We could use maximum or max. 72 00:05:01,940 --> 00:05:04,970 I'm just going to do a limit one. 73 00:05:05,540 --> 00:05:07,430 For that to work, we need to do an order by. 74 00:05:07,460 --> 00:05:09,430 So let's actually start with the order by. 75 00:05:10,310 --> 00:05:13,280 And we want to order by count. 76 00:05:13,280 --> 00:05:15,230 So I'll give it an alias. 77 00:05:15,230 --> 00:05:22,370 I'll call it total order by total and by default that will be ascending, which is not what we want. 78 00:05:22,370 --> 00:05:22,940 Right. 79 00:05:24,580 --> 00:05:25,630 We want the opposite 80 00:05:29,560 --> 00:05:32,320 getting their closer. 81 00:05:33,010 --> 00:05:38,410 Then finally, what we want to do is limit it to one and move our semicolon. 82 00:05:41,550 --> 00:05:47,940 So that gives us the name or excuse me, the ID and the image URL and the total number of likes for 83 00:05:47,940 --> 00:05:53,550 the photo of the single photo that has the most likes, which for us is 48 and this is the image of 84 00:05:53,550 --> 00:05:54,330 the photo. 85 00:05:54,780 --> 00:06:00,180 Now we want to figure out who it belongs to, who created it, so we could do a separate query. 86 00:06:00,510 --> 00:06:03,150 We could even do a subquery if you wanted to. 87 00:06:03,660 --> 00:06:06,540 But the easiest way is to do another join. 88 00:06:06,540 --> 00:06:08,230 So I'll go ahead and show that join. 89 00:06:08,250 --> 00:06:12,450 Basically, all we need to do is get the user information in there as well. 90 00:06:13,410 --> 00:06:20,970 And so we're just going to do another inner join this time on users and we're trying to connect it where 91 00:06:20,970 --> 00:06:23,240 the not the like join. 92 00:06:23,280 --> 00:06:25,260 Excuse me not the likes dot user ID. 93 00:06:25,260 --> 00:06:27,510 That's the idea of the person who liked it. 94 00:06:27,540 --> 00:06:28,530 We don't care about that. 95 00:06:28,530 --> 00:06:30,300 We want the photos. 96 00:06:30,600 --> 00:06:40,140 User ID equals user ID and now you're not going to notice anything right away because we're not displaying 97 00:06:40,140 --> 00:06:40,560 it. 98 00:06:40,560 --> 00:06:46,080 But we are joining all the user information and all that we really want is username. 99 00:06:48,720 --> 00:06:50,040 So now if we run this. 100 00:06:50,700 --> 00:06:59,310 You can see we get Zack Kemmerer 93 with ID of 145 is a person who is responsible for our most popular 101 00:06:59,310 --> 00:07:00,320 photo of all time. 102 00:07:00,330 --> 00:07:05,640 This photo Jarrett name is our Earl with a total of 48 likes.