1 00:00:00,120 --> 00:00:00,510 Okay. 2 00:00:00,660 --> 00:00:08,970 So next up, suppose getting the brains of our Instagram clone application employees and we make money 3 00:00:08,970 --> 00:00:16,200 by having sponsored content, by having advertisers post things on our app and they want to know what 4 00:00:16,230 --> 00:00:20,070 hashtags they should use, which are most popular at any given point. 5 00:00:20,250 --> 00:00:25,530 So what we want to do is just find the five most commonly used hashtags so we can tell our advertisers, 6 00:00:25,530 --> 00:00:29,670 These are the hashtags you should use on your own stuff because they're trending right now. 7 00:00:29,700 --> 00:00:30,990 So what people are looking for. 8 00:00:31,470 --> 00:00:33,750 So to do it, it's pretty straightforward. 9 00:00:34,380 --> 00:00:35,740 We have a single join. 10 00:00:35,760 --> 00:00:41,070 We're basically going to connect hashtags or tags with photo tags. 11 00:00:41,070 --> 00:00:42,570 Remember, we have those two tables. 12 00:00:42,570 --> 00:00:48,120 The one table is just the name of the tag and the other table is every time the tag is used and applied 13 00:00:48,120 --> 00:00:48,880 to a photo. 14 00:00:48,900 --> 00:00:50,190 So we want to count. 15 00:00:50,550 --> 00:00:56,040 Basically, we're on a group, all of those photo tags together, count them, but then we want to find 16 00:00:56,040 --> 00:00:57,840 the associated name of the tag. 17 00:00:58,140 --> 00:00:59,400 So let's start that. 18 00:01:01,020 --> 00:01:07,740 We'll do six most popular hashtags, five most popular hashtags. 19 00:01:08,490 --> 00:01:15,570 So if we start with just selecting start from tags as we've seen before, it's just a list of the hashtags 20 00:01:15,570 --> 00:01:21,060 themselves and then, well, an ID, a hash tag name and then create a that. 21 00:01:21,900 --> 00:01:28,420 But we've also got photo underscore tags and we've got a bunch of those 500. 22 00:01:28,440 --> 00:01:36,720 And each one is just a hash tag or a tag ID and then a photo ID that it's being applied to see if we 23 00:01:36,720 --> 00:01:37,830 can get to the top here. 24 00:01:38,010 --> 00:01:38,630 Here we go. 25 00:01:38,640 --> 00:01:45,360 So the photo ID, photo ID 14 is being tagged with whatever has tag ID one, which is sunset. 26 00:01:45,360 --> 00:01:48,960 So photo 14 has the tag sunset. 27 00:01:48,960 --> 00:01:54,990 So what we want to do is we can start if we wanted by just focusing on these and counting how many times 28 00:01:55,280 --> 00:01:56,430 each tag was used. 29 00:01:56,430 --> 00:02:00,450 But it makes it a little easier to follow if we join tags. 30 00:02:00,450 --> 00:02:02,310 So we have a name of the tag as well. 31 00:02:02,820 --> 00:02:03,840 So we'll do that. 32 00:02:03,840 --> 00:02:07,020 We'll do select start from photo tags. 33 00:02:09,229 --> 00:02:11,900 And then we'll do join tags. 34 00:02:13,080 --> 00:02:17,770 Get rid of that other stuff here on photo tags. 35 00:02:17,790 --> 00:02:18,470 Dot. 36 00:02:19,080 --> 00:02:20,160 Tag id. 37 00:02:20,190 --> 00:02:25,080 Which is this where this tag id equals tags? 38 00:02:25,080 --> 00:02:26,250 Dot id. 39 00:02:27,910 --> 00:02:30,010 So now what we see, there's a lot of stuff. 40 00:02:30,010 --> 00:02:31,180 500 rose. 41 00:02:31,630 --> 00:02:32,710 If we go up to the top. 42 00:02:33,750 --> 00:02:36,480 We can see they're already being grouped roughly. 43 00:02:37,080 --> 00:02:40,470 Not exactly, but they're being grouped roughly by the tag name. 44 00:02:41,010 --> 00:02:48,300 So we've got 14 was tagged with Sunset, 21 was tagged with Sunset for 45 was tagged sunset and so on. 45 00:02:49,230 --> 00:02:57,930 So then what we want to do is basically collapse them using group by and we could use a tag name to 46 00:02:57,930 --> 00:03:04,170 collapse them, but we could also use the tag ID, which is easier because it's just a simple integer 47 00:03:04,170 --> 00:03:06,770 rather than having to work with strings and text. 48 00:03:06,780 --> 00:03:08,870 So we'll do the tag ID. 49 00:03:08,880 --> 00:03:12,180 So that's group by tags ID. 50 00:03:13,230 --> 00:03:17,100 Now we do this, we're getting closer. 51 00:03:17,280 --> 00:03:24,690 Now we only have 21 because if in group together then finally rather than selecting star, lets select 52 00:03:24,690 --> 00:03:32,880 the tag tags dot tag name comma do these on separate lines. 53 00:03:32,880 --> 00:03:35,850 We want the count star. 54 00:03:39,290 --> 00:03:39,640 Okay. 55 00:03:39,770 --> 00:03:46,310 So we can see things like Sunset was used 19 times, photography 16 times, Sunrise 17 times. 56 00:03:46,940 --> 00:03:49,760 Then we'll go and give this an alias as well. 57 00:03:49,760 --> 00:03:54,170 Just call it as what's a good one total. 58 00:03:56,420 --> 00:03:56,740 Okay. 59 00:03:57,660 --> 00:04:03,420 Next up, we want to find the maximum so you could use Max but would be better. 60 00:04:03,810 --> 00:04:09,120 What I'm going to do is just use order by and then limit because we want the five most popular. 61 00:04:09,120 --> 00:04:11,670 So actually you couldn't use Max because we want five. 62 00:04:12,840 --> 00:04:19,470 So we'll do order by what are we ordering by this count here, which we call total order by total. 63 00:04:19,980 --> 00:04:23,490 And if we do that it will be an in ascending order. 64 00:04:23,490 --> 00:04:29,550 So we want to change that and then we want to limit it to five and that should do the trick. 65 00:04:30,510 --> 00:04:31,530 And there we go. 66 00:04:31,560 --> 00:04:37,980 We see that the top five tags right now, number one by far is smile. 67 00:04:38,760 --> 00:04:48,390 Apparently it's very popular and we've got beach at 42 party 39 fun and lol and I know for smile and 68 00:04:48,390 --> 00:04:53,580 beach I kind of engineered to be there because when I was making the data I wanted some hashtags. 69 00:04:53,580 --> 00:05:00,060 It can be used in multiple contexts and things like Beach could be used in a landscape photography shot 70 00:05:00,060 --> 00:05:06,090 alongside sunrise or sunset or landscape, but it could also be used like in a model photography shot 71 00:05:06,090 --> 00:05:08,550 alongside model or beautiful. 72 00:05:09,060 --> 00:05:13,440 It could be used in a party, you know, alongside a party hashtag. 73 00:05:13,440 --> 00:05:18,210 If it was a beach party, smile can be used in a lot of ways, so can party. 74 00:05:18,210 --> 00:05:23,820 So anyways, I tried to come up with certain tags that could be used in different ways and there we 75 00:05:23,820 --> 00:05:24,170 go. 76 00:05:24,180 --> 00:05:26,410 Those are our top five hashtags at the moment. 77 00:05:26,430 --> 00:05:29,400 Next up, you guessed it, something else.