1
00:00:00,060 --> 00:00:03,490
The next step, another challenge, this time involving genre.

2
00:00:03,510 --> 00:00:06,689
So we're finding the average rating for each of the genres.

3
00:00:06,689 --> 00:00:11,580
I think we only have three genres in the data I gave you, but it should work for all of the genres.

4
00:00:12,000 --> 00:00:18,150
So you'll need to join together information from the shows table or the series table that contains genre

5
00:00:18,240 --> 00:00:19,830
along with the ratings table.

6
00:00:19,830 --> 00:00:25,800
And then you'll need to group by genre most likely, and then do some operation to find the average

7
00:00:25,800 --> 00:00:26,360
rating.

8
00:00:26,370 --> 00:00:28,500
So I think I gave too much away.

9
00:00:28,590 --> 00:00:30,090
Let's go ahead and do it.

10
00:00:30,090 --> 00:00:36,420
So I know that we want to let's do a select start from series just to begin with.

11
00:00:36,780 --> 00:00:38,370
So does this go away?

12
00:00:38,820 --> 00:00:40,820
Oh, I see some weird glitch.

13
00:00:40,830 --> 00:00:48,960
Okay, we want to work with genre, but first we need to get genre alongside the corresponding ratings

14
00:00:48,960 --> 00:00:52,470
for each show so that we can then average the ratings by genre.

15
00:00:52,470 --> 00:00:53,940
So we'll do a join.

16
00:00:54,390 --> 00:01:05,010
We'll do something like select star from series join and then reviews on.

17
00:01:05,160 --> 00:01:07,230
And then the same thing we've done before.

18
00:01:07,260 --> 00:01:16,110
Series ID is equal to reviews, series ID that should give us everything, all the columns way more

19
00:01:16,110 --> 00:01:17,010
than we need.

20
00:01:17,100 --> 00:01:17,490
Now.

21
00:01:17,490 --> 00:01:25,500
What we want to do is only work with genre and rating, so let's slim that down genre and reading.

22
00:01:25,500 --> 00:01:27,600
We put them side by side.

23
00:01:28,380 --> 00:01:34,080
Okay, now we just need to come in with a group by which we've done many times before, group by genre,

24
00:01:34,260 --> 00:01:39,510
and then we'll calculate the average rating, so group by genre.

25
00:01:39,630 --> 00:01:43,290
And then instead of rating, we'll do average rating.

26
00:01:44,670 --> 00:01:45,630
Run this one.

27
00:01:45,810 --> 00:01:49,740
And there we go, animation, comedy and drama.

28
00:01:50,820 --> 00:01:56,930
Now, we could rename average, as I think it was average rating is what I showed here.

29
00:01:57,060 --> 00:02:05,790
AVG rating either one one and then we could round it, which I did not in this example, but we've seen

30
00:02:05,790 --> 00:02:06,630
how to do before.

31
00:02:06,630 --> 00:02:11,070
So why not round average rating to two places?

32
00:02:12,560 --> 00:02:13,340
Like that.

33
00:02:14,400 --> 00:02:17,190
Copy and paste, and that looks good.

34
00:02:17,370 --> 00:02:20,580
786816804.

35
00:02:20,820 --> 00:02:22,980
And that's it for this challenge.

36
00:02:23,070 --> 00:02:24,150
Next up, You guessed it.

37
00:02:24,150 --> 00:02:25,080
We've got another one.