WEBVTT 00:00.300 --> 00:01.030 All righty. 00:01.080 --> 00:07.680 Welcome back to our next riveting installment of asking random questions about our data base data that 00:07.680 --> 00:11.530 we inserted Yeah that's what we're doing here. 00:11.570 --> 00:17.510 So our next question here is basically started the other way around for once. 00:17.510 --> 00:22.520 What we're trying to do is figure out what is the single most liked photo in our database. 00:22.640 --> 00:25.790 And I actually don't know if Instagram has done this before. 00:25.790 --> 00:32.030 I know third parties have like people have used Instagram API to try and find the most popular Instagram 00:32.030 --> 00:33.460 of all time. 00:33.470 --> 00:35.080 That's essentially what we're trying to do. 00:35.300 --> 00:40.640 Let's say we're running a contest is the backstory I came up with but this is something that just might 00:40.640 --> 00:45.830 be nice to know internally at a company like Instagram who is responsible for the most liked photo of 00:45.830 --> 00:47.540 all time or the top ten most liked. 00:47.540 --> 00:50.110 Is there anything in common or even the top 1000. 00:50.150 --> 00:52.240 What you know tied some together. 00:52.280 --> 00:58.370 We're doing just one and we're not just trying to find the photo itself but we don't know who posted 00:58.370 --> 00:59.740 it who is responsible for it. 00:59.750 --> 01:05.190 So we can send them whatever their prizes or you know we can mention them in a blog post or something. 01:05.210 --> 01:10.790 So the first thing we need to do is just find the most popular photo to start meeting the most number 01:10.790 --> 01:11.440 of likes. 01:11.660 --> 01:15.150 And then we can worry about the username and the user afterwards. 01:15.710 --> 01:19.820 So we'll do for identify. 01:19.910 --> 01:26.630 Most popular photo and user who created it. 01:27.040 --> 01:27.600 OK. 01:27.860 --> 01:35.370 So to do that can be a select will do start to start from photos. 01:35.810 --> 01:43.010 And if we just start there you know we have 257 but no information about likes because that's a separate 01:43.010 --> 01:44.920 table of course. 01:45.040 --> 01:46.490 You are like start from like 01:50.270 --> 01:56.290 we have eight thousand seven hundred eighty two likes and they're not all displayed here for us to see. 01:56.420 --> 01:58.100 But we've got two fields. 01:58.250 --> 02:05.930 We have the ID of the person who is liking the Leiker and then we have the idea for a photo that is 02:05.930 --> 02:06.790 being liked. 02:07.010 --> 02:11.610 So what we want to work with is the photo ID we don't care about who is doing the liking. 02:11.630 --> 02:16.940 We care about you know matching the photos with the likes that correspond to them. 02:16.970 --> 02:27.620 So that's going to be adjoin and it will just be an inner join likes on and we'll do where like start 02:27.830 --> 02:33.250 photo ID equals photos dot ID. 02:33.410 --> 02:36.850 So this is going to be you know a big table here. 02:37.020 --> 02:38.280 Oh what's my air here. 02:38.420 --> 02:40.670 Extra semi-colon. 02:40.670 --> 02:41.510 Try that again. 02:42.490 --> 02:43.640 Well take a moment. 02:43.720 --> 02:46.290 We've got you know eight thousand seven hundred eighty two rows. 02:46.310 --> 02:50.700 But just like we did with Mike Smith This time the roads are significantly larger. 02:50.740 --> 02:53.560 We have a bunch of data we don't need most of that. 02:53.560 --> 03:00.070 So rather than just selecting star which gives us too much information let's whittle it down a bit and 03:00.070 --> 03:05.660 we'll just do it see photos ID and photos died. 03:05.950 --> 03:07.280 Image you are. 03:08.100 --> 03:11.290 Calm And then what do we have on the likes table. 03:11.320 --> 03:15.940 If you remember to go over to our schema here likes it's very simple. 03:15.940 --> 03:18.050 We just have our user ID and our photo ID. 03:18.160 --> 03:23.120 So we'll just do what we can do both but let's just do like user ID to start 03:26.670 --> 03:27.690 and you can see. 03:27.720 --> 03:28.040 All right. 03:28.040 --> 03:32.470 So this is all you know on our last photo ID of 257. 03:32.640 --> 03:40.780 All of these are likes for that photo and they are all you know by this user or by different users. 03:40.800 --> 03:43.620 These are already three five 10 12 14 15. 03:43.620 --> 03:45.050 We don't need that bit of information. 03:45.060 --> 03:49.830 I just want to show you just so that you can tell we're getting something from it like stable. 03:50.250 --> 03:54.460 Now what we want to do is figure out how many each photo has. 03:54.480 --> 03:57.240 So we're going to need to do a group buy. 03:58.080 --> 04:02.140 And before I do that let's just spaced this out nicely. 04:03.010 --> 04:03.570 OK. 04:03.750 --> 04:05.150 And we're going to get by. 04:05.490 --> 04:12.810 We could do the image you Earl but it's much easier just to do it with the integer of the photo ID so 04:12.810 --> 04:17.080 group by photos up here Heidi. 04:17.430 --> 04:29.160 Now if we do this we have 257 Rosenau because we only have 257 photos and then over here this is still 04:29.160 --> 04:34.690 just displaying the first ID of the user who like that photo. 04:34.860 --> 04:36.040 So that's not relevant. 04:36.200 --> 04:42.690 What we actually want to do is display the total number of users who liked it and that's just a matter 04:42.690 --> 04:43.890 of a simple count. 04:43.920 --> 04:46.290 Now that we've grouped them by photo ID 04:49.110 --> 04:53.110 and now you can see over here you know we've got photos that vary. 04:53.550 --> 04:56.460 Some of them have a bunch of like some of them not so many. 04:56.460 --> 05:01.980 Now we want to do is find the number one we could use maximum Max. 05:02.100 --> 05:07.550 I'm just going to do a limited one for that to work we need to do an order by. 05:07.590 --> 05:13.220 So let's actually start with the order by and we want to order by count. 05:13.380 --> 05:21.540 So I'll give it an alias or call it total order by total and by default that would be sending which 05:21.540 --> 05:25.390 is not what we want it we want the opposite 05:29.530 --> 05:30.520 getting there. 05:30.950 --> 05:31.480 OK. 05:31.540 --> 05:39.710 Closer than Finally what we want to do is limit it to one and move our semi colon. 05:41.260 --> 05:41.560 OK. 05:41.560 --> 05:48.360 So that gives us the name excuse me the ID image you are out and the total number of likes for the photo 05:48.360 --> 05:50.170 of the single photo that has the most likes. 05:50.170 --> 05:51.930 Which for us is 48. 05:52.150 --> 05:54.940 And this is the image of the photo. 05:54.940 --> 05:58.400 Now we want to figure out who it belongs to who created it. 05:58.510 --> 06:00.460 So we could do a separate query. 06:00.460 --> 06:06.500 We could even do a sub query if you wanted to but the easiest way is to do another join. 06:06.550 --> 06:08.320 So I'll go ahead and show that join. 06:08.320 --> 06:13.480 Basically all we need to do is get the user information in there as well. 06:13.570 --> 06:20.470 And so we're just going to do another and are joined this time on users and we're trying to connect 06:20.470 --> 06:25.240 it where the not the like joint excuse me not the likes dot user ID. 06:25.250 --> 06:27.460 That's the idea of the person who liked it. 06:27.520 --> 06:28.660 We don't care about that. 06:28.690 --> 06:35.750 We want the photos dot user ID equals user ID. 06:36.040 --> 06:41.950 And now you're not going to notice anything right away because we're not displaying it but we are joining 06:42.100 --> 06:48.800 all the user information and all that we really want is user name. 06:48.830 --> 06:57.950 Now if we run this can see if we get Zach Kemmer 93 which idea of 145 is a person who is responsible 06:58.070 --> 07:00.310 for our most popular photo of all time. 07:00.500 --> 07:01.320 This photo. 07:01.430 --> 07:05.520 Jarrett that name is or you are all with a total of 48 likes.