WEBVTT 00:00.170 --> 00:08.520 OK so next up you know suppose get in the brains of our Instagram clone application employees and we 00:08.520 --> 00:15.810 make money by having sponsored content by having advertisers post things on our app and they want to 00:15.810 --> 00:20.120 know what hashtags they should use and which are most popular at any given point. 00:20.400 --> 00:25.290 So what we want to do is just find the five most commonly used hashtags so we can tell our advertisers 00:25.650 --> 00:29.710 these are the hashtags you should use on your own stuff because they're trending right now. 00:29.720 --> 00:34.450 So people are looking for so to do it it's pretty straightforward. 00:34.470 --> 00:35.670 We have a single join. 00:35.820 --> 00:41.070 We're basically going to connect hashtags or tags with photo tags. 00:41.070 --> 00:46.440 Remember we have two tables that one table is just the name of the tag and the other table is every 00:46.440 --> 00:48.870 time the tag is used and applied to a photo. 00:49.080 --> 00:55.800 So we want to count basically we a group of those photo tags together count them but then we want to 00:55.800 --> 00:57.930 find the associated name of the tag. 00:58.230 --> 01:08.580 So let's start that will do six most popular hashtags five most popular hashtags. 01:08.640 --> 01:16.350 So if we start with just selecting star tags as we've seen before it's just a list of the hashtags themselves. 01:16.440 --> 01:22.010 And then well NRD hash tag name and then created it. 01:22.020 --> 01:31.140 But we've also got photo underscore tags and we've got a bunch of those 500 and each one is just a hash 01:31.140 --> 01:37.990 tag or a tag ID and then a photo ID that it's being applied to see if we can get to the top here. 01:38.050 --> 01:38.530 Here we go. 01:38.790 --> 01:45.540 So the photo ID photo I.D. 14 is being tagged with whatever hash tag I.D. one wishes sunset. 01:45.540 --> 01:49.130 So photo 14 has a tag sunset. 01:49.140 --> 01:54.630 So what we want to do is we can start if we want and by just focusing on these and counting how many 01:54.630 --> 02:00.470 times each tag was used but it makes it a little easier to follow if we join tags. 02:00.480 --> 02:02.520 So we have a name of the tag as well. 02:02.970 --> 02:03.810 So we'll do that. 02:03.900 --> 02:12.910 We'll do select star from photo tags and then we'll do join tags. 02:13.080 --> 02:20.180 Get rid of the other stuff here on photo tags dot tag ID. 02:20.280 --> 02:30.470 So is this where this tag I.D. equals tags dot I.D. So now we see there's a lot of stuff five hundred 02:30.470 --> 02:36.850 rows if we go up to the top we can either already being grouped roughly. 02:37.240 --> 02:40.630 Not exactly but they're being grouped roughly by the tag name. 02:41.020 --> 02:47.590 So we've got you know 14 was tagged with sunset 21 or Taghreed sunset 40 or 45 was tagged to sunset 02:47.620 --> 02:49.320 and so on. 02:49.320 --> 02:54.470 So then what we want to do is basically collapse them using group by. 02:55.030 --> 03:02.500 And we could use a tag name to collapse them but we could also use the tag ID which is easier because 03:02.500 --> 03:06.780 it's just a simple integer rather than having to work with strings and text. 03:06.970 --> 03:08.940 So we'll do the tag that ID. 03:08.950 --> 03:16.030 So that group by tags that Id Now we do this. 03:16.030 --> 03:17.360 We're getting closer. 03:17.440 --> 03:20.800 Now we only have 21 because they've been grouped together. 03:20.800 --> 03:31.290 Then finally rather than selecting star let's select the tag tags that tag name comma. 03:31.750 --> 03:32.870 Do these on separate lines. 03:32.880 --> 03:35.640 We want the count Star 03:39.340 --> 03:46.690 OK so we can see things like sunset was used 19 times photography 16 times sunrise 17 times. 03:47.080 --> 03:57.770 Then we'll go and give an alias as well just call it as well to get total. 03:57.780 --> 04:00.680 Next up we want to find the maximum. 04:00.810 --> 04:03.540 So you could use max but would be better. 04:03.830 --> 04:09.490 But I'm going to do is just use ORDER BY and limit because we want the five most popular so actually 04:09.480 --> 04:12.040 you couldn't use Max because we want 5. 04:12.920 --> 04:19.770 So we'll do order by what are we ordering by this count here which we called Total Order by total. 04:20.010 --> 04:23.450 And if we do that it will be in in ascending order. 04:23.460 --> 04:25.260 So we want to change that. 04:25.800 --> 04:30.350 And then we want to limit it to 5 and that should do the trick. 04:30.540 --> 04:31.550 And there we go. 04:31.770 --> 04:38.460 We see that the top five tagged right now number one by far is smile. 04:38.820 --> 04:45.670 Apparently it's very popular and we've got beach at 42 party thirty nine fun and EHLO well. 04:46.230 --> 04:51.420 And I know for you know smile and beach are kind of engineered to be there because when I was making 04:51.420 --> 04:57.180 the data I wanted some hash tags that could be used in multiple contexts and things like you know Beach 04:57.420 --> 05:03.930 could be used in landscape photography shot alongside sunrise or sunset or landscape but it can also 05:03.930 --> 05:08.970 be used like in a model photography shot alongside model beautiful. 05:09.030 --> 05:14.900 It could be used in a party you know alongside a party hash tag if it was a beach party. 05:15.150 --> 05:17.180 Smile can be used in a lot of ways. 05:17.220 --> 05:18.180 So can party. 05:18.300 --> 05:23.220 So anyways I tried to come up with certain tags that could be used in different ways. 05:23.340 --> 05:24.120 And there we go. 05:24.180 --> 05:26.520 Those are our top five hashtags at the moment. 05:26.520 --> 05:29.190 Next up you guessed it something else.