1 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 2 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 3 00:00:16,230 --> 00:00:20,070 hashtags they should use, which are most popular at any given point. 4 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 5 00:00:25,530 --> 00:00:29,680 These are the hashtags you should use on your own stuff because they're trending right now. 6 00:00:29,700 --> 00:00:30,990 So what people are looking for. 7 00:00:31,440 --> 00:00:33,750 So to do it, it's pretty straightforward. 8 00:00:34,380 --> 00:00:35,760 We have a single join. 9 00:00:35,790 --> 00:00:41,070 We're basically going to connect hashtags or tags with photo tags. 10 00:00:41,080 --> 00:00:42,590 Remember, we have those two tables. 11 00:00:42,600 --> 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 12 00:00:48,120 --> 00:00:48,880 to a photo. 13 00:00:48,900 --> 00:00:50,190 So we want to count. 14 00:00:50,550 --> 00:00:52,440 Basically, we want a group. 15 00:00:52,440 --> 00:00:57,270 All of those photo tags together, count them, but then we want to find the associated name of the 16 00:00:57,270 --> 00:00:57,840 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 star 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,450 But we've also got photo underscore tags and we've got a bunch of those 500. 22 00:01:28,470 --> 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:37,980 --> 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,240 --> 00:02:01,500 each tag was used, but it makes it a little easier to follow if we join tags so we have a name of the 29 00:02:01,500 --> 00:02:02,310 tag as well. 30 00:02:02,820 --> 00:02:03,840 So we'll do that. 31 00:02:03,840 --> 00:02:07,020 We'll do select start from photo tags. 32 00:02:09,199 --> 00:02:11,900 And then we'll do join tags. 33 00:02:13,080 --> 00:02:17,760 Get rid of that other stuff here on photo tags. 34 00:02:17,790 --> 00:02:18,470 Dot. 35 00:02:19,110 --> 00:02:20,160 Tag ID. 36 00:02:20,190 --> 00:02:26,250 Which is this where this tag ID equals tags dot ID. 37 00:02:27,910 --> 00:02:30,010 So now what we see, there's a lot of stuff. 38 00:02:30,010 --> 00:02:31,180 500 rose. 39 00:02:31,630 --> 00:02:32,710 If we go up to the top. 40 00:02:33,780 --> 00:02:36,480 We can see they're already being grouped roughly. 41 00:02:37,110 --> 00:02:40,470 Not exactly, but they're being grouped roughly by the tag name. 42 00:02:41,010 --> 00:02:44,250 So we've got 14 was tagged with Sunset. 43 00:02:44,340 --> 00:02:45,210 21 was tagged. 44 00:02:45,300 --> 00:02:48,300 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,780 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:20,340 Now we only have 21 because they've been grouped together. 52 00:03:20,670 --> 00:03:30,720 Then finally, rather than selecting Star, lets select the tag tags dot tag name comma. 53 00:03:31,770 --> 00:03:32,870 Do these on separate lines. 54 00:03:32,880 --> 00:03:35,850 We want the count star. 55 00:03:39,290 --> 00:03:39,710 Okay. 56 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. 57 00:03:46,940 --> 00:03:49,760 Then we'll go and give this an alias as well. 58 00:03:49,760 --> 00:03:54,170 Just call it as what's a good one total. 59 00:03:56,450 --> 00:03:56,780 Okay. 60 00:03:57,660 --> 00:04:00,750 Next up, we want to find the maximum. 61 00:04:00,750 --> 00:04:03,420 So you could use Max, but would be better. 62 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. 63 00:04:09,120 --> 00:04:11,670 So actually you couldn't use Max because we want five. 64 00:04:12,840 --> 00:04:14,060 So we'll do order by. 65 00:04:14,070 --> 00:04:19,500 What are we ordering by this count here, which we call total order by total. 66 00:04:19,980 --> 00:04:23,490 And if we do that, it will be an in ascending order. 67 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. 68 00:04:30,510 --> 00:04:31,530 And there we go. 69 00:04:31,560 --> 00:04:37,980 We see that the top five tags right now, number one by far is smile. 70 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 71 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. 72 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 73 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 74 00:05:06,090 --> 00:05:08,550 alongside model or beautiful. 75 00:05:09,060 --> 00:05:13,440 It could be used in a party, you know, alongside a party hashtag. 76 00:05:13,440 --> 00:05:14,760 If it was a beach party. 77 00:05:15,000 --> 00:05:18,210 Smile can be used in a lot of ways, so can party. 78 00:05:18,210 --> 00:05:23,280 So anyways, I tried to come up with certain tags that could be used in in different ways. 79 00:05:23,280 --> 00:05:24,170 And there we go. 80 00:05:24,180 --> 00:05:26,430 Those are our top five hashtags at the moment. 81 00:05:26,460 --> 00:05:29,400 Next up, you guessed it something else.