1 00:00:00,090 --> 00:00:00,600 Okay. 2 00:00:00,600 --> 00:00:04,410 So next up, we have another nice clean table, just three results. 3 00:00:04,410 --> 00:00:09,570 And as you can see, what we're doing here is grabbing things based off of genre. 4 00:00:09,720 --> 00:00:13,320 So we only have three fortunately animation, comedy and drama. 5 00:00:13,320 --> 00:00:18,930 And then what we're doing is computing the average review or the average rating for each of those genres. 6 00:00:18,930 --> 00:00:22,740 So this will be a little similar to what we've already done earlier in this problem set. 7 00:00:22,890 --> 00:00:28,410 The main difference is that we're working with genre now rather than just the title of a given series. 8 00:00:30,150 --> 00:00:32,640 One thing I definitely need to correct the record here. 9 00:00:32,640 --> 00:00:34,080 I called this challenge five. 10 00:00:34,170 --> 00:00:35,040 I was wrong. 11 00:00:35,640 --> 00:00:38,160 After four, after three comes four. 12 00:00:38,340 --> 00:00:40,080 Now we're on challenge five 13 00:00:43,050 --> 00:00:47,040 and we'll just call this genre average ratings. 14 00:00:48,270 --> 00:00:54,420 So to get this information from our three tables, the two that we're that we need to identify that 15 00:00:54,420 --> 00:01:00,480 we're working with are the series table because that's where our genre information is stored. 16 00:01:00,480 --> 00:01:03,720 And then the reviews table, that's where our ratings information is stored. 17 00:01:03,720 --> 00:01:06,780 So we don't care about the reviewers, the people who reviewed them. 18 00:01:06,780 --> 00:01:07,640 That doesn't matter. 19 00:01:07,650 --> 00:01:13,980 All we need is the genres and then the ratings, so we can start with either one. 20 00:01:14,250 --> 00:01:20,910 Let's start with Star from series just like that. 21 00:01:22,230 --> 00:01:22,740 Okay. 22 00:01:23,310 --> 00:01:29,730 So all we're going to want to work with is the genre portion, but we're going to need to join with 23 00:01:29,730 --> 00:01:32,190 the reviews table, so we'll just do that. 24 00:01:32,190 --> 00:01:35,670 Now select start from series join reviews. 25 00:01:35,820 --> 00:01:47,580 Hopefully this seems familiar now on series dot ID equals reviews dot series ID and if we leave the 26 00:01:47,580 --> 00:01:49,770 star in there, we're going to get a lot of stuff. 27 00:01:50,920 --> 00:01:52,630 So we don't need all of that. 28 00:01:53,530 --> 00:01:58,960 Let's just pick out a few things like title, genre and rating. 29 00:02:02,410 --> 00:02:08,530 So now we can see we've got all the ratings for Archer, all animation, of course, because they're 30 00:02:08,530 --> 00:02:13,990 the same exact row here with a relevant rating appended. 31 00:02:14,290 --> 00:02:15,650 So we've joined them together. 32 00:02:15,670 --> 00:02:19,700 It's an inner join and that's what we want. 33 00:02:19,720 --> 00:02:23,590 We don't want to be working with non-existent ratings. 34 00:02:23,680 --> 00:02:29,740 We don't want to take let's say, what was it, Pushing Daisies, which didn't have any reviews? 35 00:02:29,770 --> 00:02:32,260 Well, that's a comedy, I believe. 36 00:02:32,290 --> 00:02:33,190 Maybe it's a drama. 37 00:02:33,190 --> 00:02:33,970 I don't remember. 38 00:02:33,970 --> 00:02:39,580 But whatever it is, we don't want to try and average nul into the average rating for drama. 39 00:02:39,580 --> 00:02:40,960 So the average rating for comedy. 40 00:02:40,960 --> 00:02:42,970 So we don't want to do a left join. 41 00:02:42,970 --> 00:02:43,850 We want inner. 42 00:02:45,190 --> 00:02:48,640 So we've got this on series ID, blah, blah, blah, blah, blah. 43 00:02:49,180 --> 00:02:53,230 Now all we need to do is group things by genre. 44 00:02:53,890 --> 00:02:57,400 That's what we're trying to do here rather than by title, grouped by genre. 45 00:03:01,260 --> 00:03:07,530 And if we do that, we should see this is immediately reduced down to three rows, although remember 46 00:03:07,530 --> 00:03:11,100 that it's just kind of hiding all the data that lies beneath. 47 00:03:11,700 --> 00:03:16,260 So we have animation here, but it doesn't mean that there's only one. 48 00:03:16,380 --> 00:03:21,540 It's just that group by when you when you use a group by it's displayed in a very weird, awkward way. 49 00:03:21,540 --> 00:03:23,700 That I think is kind of confusing. 50 00:03:23,910 --> 00:03:30,630 But what we want to do is rather then we can get rid of the title two rather than just genre and rating. 51 00:03:31,230 --> 00:03:33,480 We just want average rating. 52 00:03:35,260 --> 00:03:36,640 And that should do it for us. 53 00:03:37,380 --> 00:03:44,760 And we get the average ratings and there's an alias, so you can add that in like that. 54 00:03:46,020 --> 00:03:46,890 And that's it. 55 00:03:47,490 --> 00:03:48,900 So now we get animation. 56 00:03:49,140 --> 00:03:51,060 It's the lowest rated, still quite high. 57 00:03:51,690 --> 00:03:54,660 Comedy is the highest rated 8.1. 58 00:03:54,840 --> 00:03:58,050 And drama is right in the middle 8.0. 59 00:03:58,530 --> 00:04:03,640 And I suppose this is as good a time as any to introduce the round function. 60 00:04:03,660 --> 00:04:08,430 We haven't used it yet, but we're getting a lot of decimals here and we don't we don't care about them. 61 00:04:08,430 --> 00:04:16,380 Let's just round it to let's do two decimal places so it looks like this round and I'll do it on separate 62 00:04:16,380 --> 00:04:17,190 lines here. 63 00:04:17,610 --> 00:04:23,310 So we have round average rating, but we don't just say round average rating. 64 00:04:23,310 --> 00:04:31,200 We have to supply what we want to round it to two digits for us and then we'll use adds average rating. 65 00:04:31,320 --> 00:04:37,410 So I could have done it all in one line, a gigantic gross line in my opinion. 66 00:04:37,410 --> 00:04:39,750 So it's a little better to break it up like this. 67 00:04:40,350 --> 00:04:43,260 It takes up more space, but it's a price we pay. 68 00:04:43,890 --> 00:04:48,240 So round the average two digits and call it average rating. 69 00:04:48,750 --> 00:04:49,830 Now let's double check. 70 00:04:49,830 --> 00:04:50,640 That works. 71 00:04:50,910 --> 00:04:51,840 And there we go. 72 00:04:52,080 --> 00:04:52,920 Much easier. 73 00:04:52,920 --> 00:04:56,010 You didn't have to do that, of course, but I figured it was a good thing to show.