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,820 And I actually don't know if Instagram has done this before. 8 00:00:25,820 --> 00:00:32,030 I know third parties have like people have used Instagram API to try and find the most popular Instagram 9 00:00:32,030 --> 00:00:32,960 of all time. 10 00:00:33,470 --> 00:00:35,200 That's essentially what we're trying to do. 11 00:00:35,210 --> 00:00:38,900 Let's say we're running a contest is the back story I came up with. 12 00:00:38,900 --> 00:00:43,730 But this is something that just might be nice to know internally at a company like Instagram who is 13 00:00:43,730 --> 00:00:47,570 responsible for the most liked 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,890 What ties them together? 16 00:00:52,130 --> 00:00:53,330 We're doing just one. 17 00:00:53,330 --> 00:00:59,510 And we're not just trying to find the photo itself, but we all know who posted it, who was responsible 18 00:00:59,510 --> 00:01:04,849 for it, so we can send them whatever their prize is or we can mention them 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,340 We have 8782 likes and they're not all displayed here for us to see. 27 00:01:56,340 --> 00:01:58,100 But we've got two fields. 28 00:01:58,110 --> 00:02:06,240 We have the ID of the person who is liking the liker and then we have the ID of the photo that is being 29 00:02:06,240 --> 00:02:06,860 liked. 30 00:02:06,870 --> 00:02:11,430 So what we want to work with is the photo ID We don't care about who is doing the liking. 31 00:02:11,430 --> 00:02:16,860 We care about matching the photos with the likes that correspond to them. 32 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 33 00:02:27,780 --> 00:02:36,780 photo ID equals photos dot ID So this is going to be a big table here. 34 00:02:36,930 --> 00:02:38,340 Oh, what's my error here. 35 00:02:38,370 --> 00:02:39,630 Extra semicolon. 36 00:02:40,680 --> 00:02:41,550 Try that again. 37 00:02:42,490 --> 00:02:43,450 We'll take a moment. 38 00:02:43,570 --> 00:02:47,740 We've got 8782 rows, but just like we did with likes. 39 00:02:47,740 --> 00:02:50,590 But this time the rows are significantly larger. 40 00:02:50,620 --> 00:02:51,820 We have a bunch of data. 41 00:02:52,000 --> 00:02:53,590 We don't need most of that. 42 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 43 00:02:59,560 --> 00:03:09,340 and we'll just do, let's see, photos, ID and photos, dot image, URL, comma and then what do we 44 00:03:09,340 --> 00:03:11,320 have on the likes table? 45 00:03:11,320 --> 00:03:15,820 If you remember, if you go over to our schema here, likes is very simple. 46 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. 47 00:03:20,830 --> 00:03:23,350 Let's just do like user ID to start. 48 00:03:26,610 --> 00:03:27,660 And you can see. 49 00:03:27,660 --> 00:03:32,510 All right, so this is all on our last photo with ID of 257. 50 00:03:32,520 --> 00:03:35,310 All of these are likes for that photo. 51 00:03:35,970 --> 00:03:40,710 And they are all by this user or by different users. 52 00:03:40,740 --> 00:03:43,470 User D three, five, ten, 12, 14, 15. 53 00:03:43,470 --> 00:03:45,060 We don't need that bit of information. 54 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. 55 00:03:50,070 --> 00:03:54,300 Now what we want to do is figure out how many each photo has. 56 00:03:54,300 --> 00:03:56,460 So we're going to need to do a group by. 57 00:03:57,900 --> 00:04:05,130 And before I do that, let's just spaced this out nicely and we're going to group by. 58 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. 59 00:04:12,510 --> 00:04:18,690 So group by photos up here dot ID now if we do this. 60 00:04:20,640 --> 00:04:25,710 We have 257 rows now because we only have 257 photos. 61 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. 62 00:04:34,710 --> 00:04:36,010 So that's not relevant. 63 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 64 00:04:42,720 --> 00:04:46,470 of a simple count now that we've grouped them by photo state ID. 65 00:04:49,100 --> 00:04:53,000 And now you can see over here we've got photos that vary. 66 00:04:53,450 --> 00:04:55,910 Some of them have a bunch of like some of them not so many. 67 00:04:56,300 --> 00:04:59,510 Now all we want to do is find the number one. 68 00:04:59,660 --> 00:05:01,920 We could use maximum or max. 69 00:05:01,940 --> 00:05:04,970 I'm just going to do a limit one. 70 00:05:05,540 --> 00:05:07,430 For that to work, we need to do an order by. 71 00:05:07,460 --> 00:05:09,400 So let's actually start with the order by. 72 00:05:10,310 --> 00:05:13,280 And we want to order by count. 73 00:05:13,280 --> 00:05:15,220 So I'll give it an alias. 74 00:05:15,230 --> 00:05:22,370 I'll call it total order by total and by default that would be ascending, which is not what we want. 75 00:05:22,370 --> 00:05:22,940 Right. 76 00:05:24,550 --> 00:05:25,630 We want the opposite. 77 00:05:29,560 --> 00:05:32,320 Getting their closer. 78 00:05:33,010 --> 00:05:38,410 Then finally, what we want to do is limit it to one and move our semicolon. 79 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 80 00:05:47,940 --> 00:05:51,840 the photo of the single photo that has the most likes, which for us is 48. 81 00:05:51,990 --> 00:05:54,330 And this is the image of the photo. 82 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. 83 00:06:00,510 --> 00:06:03,150 We could even do a subquery if you wanted to. 84 00:06:03,660 --> 00:06:06,540 But the easiest way is to do another join. 85 00:06:06,540 --> 00:06:08,230 So I'll go ahead and show that join. 86 00:06:08,250 --> 00:06:12,450 Basically, all we need to do is get the user information in there as well. 87 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 88 00:06:20,970 --> 00:06:23,240 the not the like join. 89 00:06:23,280 --> 00:06:27,510 Excuse me not the likes dot user ID that's the idea of the person who liked it. 90 00:06:27,540 --> 00:06:28,530 We don't care about that. 91 00:06:28,530 --> 00:06:39,000 We want the photos user ID equals user ID and now you're not going to notice anything right away because 92 00:06:39,000 --> 00:06:40,560 we're not displaying it. 93 00:06:40,560 --> 00:06:46,110 But we are joining all the user information and all that we really want is username. 94 00:06:48,750 --> 00:06:50,070 So now if we run this. 95 00:06:50,720 --> 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 96 00:06:59,310 --> 00:07:00,320 photo of all time. 97 00:07:00,330 --> 00:07:05,640 This photo Jarrett name is Earl with a total of 48 likes.