1 00:00:00,210 --> 00:00:00,810 Okay. 2 00:00:00,870 --> 00:00:07,350 So next up, we have a return to these long, annoying tables with quite a few fields on this one. 3 00:00:07,530 --> 00:00:13,410 This is sort of like analytics for our reviewers, and what we're doing here is compiling statistics 4 00:00:13,410 --> 00:00:13,770 on them. 5 00:00:13,770 --> 00:00:17,430 So, Thomas Stoneman, how many reviews has written? 6 00:00:17,430 --> 00:00:18,060 Five. 7 00:00:18,870 --> 00:00:19,980 What's the minimum? 8 00:00:19,980 --> 00:00:21,750 He's assigned 7.0. 9 00:00:21,780 --> 00:00:24,000 The maximum review is a 9.5. 10 00:00:24,030 --> 00:00:26,130 His average is 8.02. 11 00:00:26,430 --> 00:00:33,000 And then a status which is saying that he's active and that's determined based off of the if the user 12 00:00:33,000 --> 00:00:34,340 has any reviews. 13 00:00:34,350 --> 00:00:38,410 So Marlon Crawford has zero. 14 00:00:38,430 --> 00:00:41,490 So he is inactive, but everyone else is active. 15 00:00:41,730 --> 00:00:43,320 So we've got a lot of pieces here. 16 00:00:43,320 --> 00:00:50,790 And probably the most crucial thing to point out is that we have Marlon Crawford, who has zero reviews, 17 00:00:51,000 --> 00:00:52,920 which we need to pay attention to. 18 00:00:52,950 --> 00:00:57,450 We have to pay special attention to that because there's not going to be a natural join if we do an 19 00:00:57,450 --> 00:00:57,900 inner join. 20 00:00:57,900 --> 00:01:04,560 So we'll need to do a left join, which will highlight the fact that he has no reviews and then the 21 00:01:04,560 --> 00:01:05,940 rest is not too difficult. 22 00:01:05,940 --> 00:01:10,230 Count Min max average should be pretty comfortable with that status. 23 00:01:11,220 --> 00:01:16,980 We need to do some logic there, so let's start by adding in challenge. 24 00:01:19,710 --> 00:01:20,460 Six. 25 00:01:21,540 --> 00:01:25,890 We'll just call this reviewer stats. 26 00:01:27,060 --> 00:01:31,320 So the tables we're working with, we've got the reviewer table, which is where we're pulling things 27 00:01:31,320 --> 00:01:32,130 like the name. 28 00:01:32,490 --> 00:01:37,350 And then we've got the reviews table, which is where we're pulling Count Min Well, it's where we're 29 00:01:37,350 --> 00:01:42,750 pulling the, the actual ratings themselves and then we're basically getting this data from there. 30 00:01:43,320 --> 00:01:45,570 So we don't need to worry about the series table. 31 00:01:45,570 --> 00:01:46,710 We're not working with that. 32 00:01:47,040 --> 00:01:51,420 We will be in the next the final challenge here, we're going to put all three together, but in this 33 00:01:51,420 --> 00:01:52,260 case we're not. 34 00:01:52,920 --> 00:01:55,770 So we'll start with a select star. 35 00:01:55,870 --> 00:01:58,080 Let's just do well, we'll do star to start. 36 00:01:58,170 --> 00:02:00,990 It's like star from reviewers. 37 00:02:03,200 --> 00:02:04,760 We get all of our reviewers here. 38 00:02:04,760 --> 00:02:05,570 Perfect. 39 00:02:05,720 --> 00:02:07,070 We want all of them. 40 00:02:07,430 --> 00:02:09,820 So that will be the left part of our joint. 41 00:02:09,830 --> 00:02:13,370 If we remember the two circles, A and B, this will be circle A right here. 42 00:02:13,370 --> 00:02:15,110 We want all of this to show up. 43 00:02:15,320 --> 00:02:18,260 Even though Marlin doesn't have any reviews. 44 00:02:18,260 --> 00:02:27,530 Then we're going to do a join join reviews on reviewers dot id. 45 00:02:27,530 --> 00:02:29,810 So wear the reviewer id like this. 46 00:02:29,810 --> 00:02:38,150 One, two, three, four, five, six is equal to the reviews dot reviewer ID. 47 00:02:38,420 --> 00:02:45,680 And then in the case of Marlin, there is no reviewer ID equal to seven in the reviews table, so we'll 48 00:02:45,680 --> 00:02:48,140 get null, null, null and so on. 49 00:02:49,290 --> 00:02:49,820 Okay. 50 00:02:50,100 --> 00:02:50,940 Let's try it. 51 00:02:52,630 --> 00:02:55,900 And if we take a look at the results here, we don't get those NOLs. 52 00:02:55,900 --> 00:02:59,580 And that's because we didn't specify that it's a left join. 53 00:02:59,590 --> 00:03:00,820 We left that off. 54 00:03:01,450 --> 00:03:04,720 And now at the end, we have Marlon Crawford. 55 00:03:04,720 --> 00:03:06,280 No, no, no, no. 56 00:03:06,460 --> 00:03:14,200 Which will need to make use of to do things like this where we have zero min max average or all zero 57 00:03:14,200 --> 00:03:15,610 and then inactive. 58 00:03:15,940 --> 00:03:21,340 So we'll have to kind of make special exceptions for Marlon or for anybody with NULL. 59 00:03:22,240 --> 00:03:22,660 Okay. 60 00:03:22,660 --> 00:03:24,970 So now let's start whittling down what we need. 61 00:03:24,970 --> 00:03:26,170 We want first name. 62 00:03:27,520 --> 00:03:28,540 We want last name. 63 00:03:30,160 --> 00:03:37,960 So what we have here right then we want things like count min, max and average which require us to 64 00:03:37,960 --> 00:03:38,920 group by. 65 00:03:38,950 --> 00:03:44,740 But let's start by just putting rating here without grouping and just verify what we need. 66 00:03:44,740 --> 00:03:45,520 Exactly. 67 00:03:46,720 --> 00:03:51,760 Okay, so here's all the reviews and we want to group them together and we could group based off of 68 00:03:51,760 --> 00:03:53,290 first name and last name. 69 00:03:53,560 --> 00:03:55,780 But that's not guaranteed to be unique, of course. 70 00:03:55,780 --> 00:04:04,900 So it's better to group based off of the reviewer's ID so we can do that now group by reviewer's ID 71 00:04:04,930 --> 00:04:05,860 just like that. 72 00:04:06,400 --> 00:04:08,050 And if we do that, whoops. 73 00:04:08,170 --> 00:04:09,610 Didn't mean to expand that. 74 00:04:11,460 --> 00:04:11,850 Again. 75 00:04:11,850 --> 00:04:14,160 Our group Bye is working its weird magic. 76 00:04:15,060 --> 00:04:16,529 It's working behind the scenes. 77 00:04:16,529 --> 00:04:19,829 So now we can do things like average, for instance. 78 00:04:19,829 --> 00:04:20,700 Let's start there. 79 00:04:21,990 --> 00:04:26,580 Well, actually, even easier is counts and we'll just count the ratings. 80 00:04:27,540 --> 00:04:29,310 So behind the scenes, it's grouped them. 81 00:04:29,310 --> 00:04:31,970 Remember, it went from this here. 82 00:04:31,980 --> 00:04:33,570 So if we take a look at like. 83 00:04:35,050 --> 00:04:37,300 These, they were all grouped together. 84 00:04:37,300 --> 00:04:40,750 So under Thomas Stoneman, there's one, two, three, four, five. 85 00:04:42,810 --> 00:04:44,480 But we only see Thomas Stoneman. 86 00:04:44,490 --> 00:04:48,600 We don't get five results, but they're there behind the scenes. 87 00:04:48,810 --> 00:04:51,930 So we'll do count rating. 88 00:04:53,430 --> 00:04:55,520 And you can see it looks good. 89 00:04:55,530 --> 00:04:57,510 We get five nine. 90 00:04:57,510 --> 00:05:03,810 You can see Domingo and Colt have written a ton of reviews and Marlon just hasn't written any, so that's 91 00:05:03,810 --> 00:05:04,440 a start. 92 00:05:05,250 --> 00:05:07,290 Then we've got Min and Max. 93 00:05:07,470 --> 00:05:09,180 So those are quite simple to. 94 00:05:10,910 --> 00:05:12,470 Just let's start with min. 95 00:05:12,500 --> 00:05:15,110 The min rating they've written. 96 00:05:16,700 --> 00:05:21,530 And that works for everybody except for good old marlin causing us problems. 97 00:05:22,220 --> 00:05:26,120 And we should be relatively comfortable with fixing this at this point. 98 00:05:26,420 --> 00:05:28,490 Notice that I just sent it to be zero. 99 00:05:28,910 --> 00:05:30,110 You could have set it to be anything. 100 00:05:30,110 --> 00:05:35,930 You could leave it as null, but let's make it zero to match the chart so we can use our if null. 101 00:05:38,330 --> 00:05:41,720 MIN rating, then we'll set it to be zero. 102 00:05:42,470 --> 00:05:47,840 And if I leave it like this, our table is going to be kind of ugly because we have this if null up 103 00:05:47,840 --> 00:05:49,220 here, we don't need all the space. 104 00:05:49,220 --> 00:05:54,320 So let's call this one as count and this one as min. 105 00:05:55,580 --> 00:05:59,690 That should cut down on the space looking better. 106 00:06:00,680 --> 00:06:01,970 And then the same thing for Max. 107 00:06:02,150 --> 00:06:06,260 We can just copy and just change min to Max. 108 00:06:08,260 --> 00:06:08,860 Okay. 109 00:06:09,700 --> 00:06:10,810 So we got Min and Max. 110 00:06:10,810 --> 00:06:12,400 Let's just triple check. 111 00:06:15,090 --> 00:06:15,900 There we go. 112 00:06:16,110 --> 00:06:17,610 Count Min Max. 113 00:06:18,180 --> 00:06:23,640 Then we got average, which by now you should be comfortable with doing as well. 114 00:06:23,730 --> 00:06:30,240 It's the same thing as average, but we'll run into that same problem with NULL. 115 00:06:30,510 --> 00:06:31,880 We don't want null there. 116 00:06:31,890 --> 00:06:33,090 We just want zero. 117 00:06:33,540 --> 00:06:36,810 So if null once again. 118 00:06:39,010 --> 00:06:40,540 Just like that. 119 00:06:44,480 --> 00:06:45,380 We'll copy it. 120 00:06:48,080 --> 00:06:52,970 And we've got first almost everything, except now we need to work with status. 121 00:06:53,630 --> 00:06:57,320 And status is a little different because there's some logic here. 122 00:06:57,320 --> 00:06:58,590 And what's it based off of? 123 00:06:58,610 --> 00:06:59,990 Well, it's based off of count. 124 00:06:59,990 --> 00:07:06,110 So if count is more than one or greater than or equal to one, then we'll call that user active or the 125 00:07:06,110 --> 00:07:07,160 reviewer active. 126 00:07:07,220 --> 00:07:09,800 Otherwise it's inactive. 127 00:07:09,800 --> 00:07:10,700 Or we could switch it around. 128 00:07:10,700 --> 00:07:16,810 We could say if count is equal to exactly zero, then status is inactive. 129 00:07:16,820 --> 00:07:18,650 Otherwise they're active. 130 00:07:19,220 --> 00:07:23,720 And to do it, we could use a case statement and we can start with that. 131 00:07:24,320 --> 00:07:26,480 What we're working with again is count. 132 00:07:26,960 --> 00:07:34,850 So let's do this at the bottom, our case and I'd like to do end as and we'll call this status. 133 00:07:36,170 --> 00:07:36,670 Okay. 134 00:07:37,340 --> 00:07:44,780 And our first thing we'll check for in case is when count rating and we can do when it's greater than 135 00:07:44,780 --> 00:07:45,950 or equal to one. 136 00:07:47,740 --> 00:07:53,020 Then we'll say active status will be active. 137 00:07:53,590 --> 00:07:54,310 Else? 138 00:07:55,840 --> 00:07:57,970 Can we need then here. 139 00:07:58,750 --> 00:08:01,990 Otherwise in active. 140 00:08:03,800 --> 00:08:04,980 Active and active. 141 00:08:06,920 --> 00:08:08,150 Let's try that. 142 00:08:10,100 --> 00:08:10,490 Okay. 143 00:08:10,520 --> 00:08:11,540 It works just fine. 144 00:08:11,990 --> 00:08:16,190 But I did say there's another thing that we can do, which I also haven't shown you until now. 145 00:08:16,190 --> 00:08:21,140 I didn't want to overload you with a bunch of different ways of doing logic, but for simpler case statements, 146 00:08:21,140 --> 00:08:25,880 we can use another function or another logical function which is called. 147 00:08:25,880 --> 00:08:31,340 If so, I'm just going to replace this with if I'll show you what it looks like, we just say if and 148 00:08:31,340 --> 00:08:33,590 then the condition that we're looking for. 149 00:08:33,590 --> 00:08:41,000 So if count rating is greater than or equal to one, then the second argument is what we want to spit 150 00:08:41,000 --> 00:08:41,539 out. 151 00:08:41,539 --> 00:08:45,860 And then the third one is the else condition or the else argument. 152 00:08:45,860 --> 00:08:50,540 So these are equivalent right here as long as I put status. 153 00:08:51,080 --> 00:08:53,300 So let me make that a little bit easier to see. 154 00:08:53,990 --> 00:08:57,290 This right here is saying, okay, if count rating. 155 00:08:57,290 --> 00:09:03,530 So if the count of all the ratings per each user or each reviewer is greater than or equal to one, 156 00:09:03,530 --> 00:09:06,260 then status is active. 157 00:09:06,260 --> 00:09:07,640 If that's not true. 158 00:09:07,790 --> 00:09:09,170 Status is inactive. 159 00:09:09,650 --> 00:09:14,120 So this works great when we only have these two things that we're working with, right? 160 00:09:14,180 --> 00:09:15,320 Active and inactive. 161 00:09:15,410 --> 00:09:20,060 But if we wanted to add another thing in, it's a little more complex. 162 00:09:20,060 --> 00:09:23,300 Like if we wanted to say, okay, if it's greater than one, then they're active. 163 00:09:23,300 --> 00:09:29,270 And if it's greater than ten or greater than equal to ten, they're a power user or something, otherwise 164 00:09:29,270 --> 00:09:30,230 they're inactive. 165 00:09:30,260 --> 00:09:34,880 It's much easier to use a case statement for that, so I can show you that. 166 00:09:34,880 --> 00:09:42,230 But I'm going to get rid of this first and just make sure that our new edition works the same way. 167 00:09:43,320 --> 00:09:44,320 Looks good to me. 168 00:09:44,340 --> 00:09:46,030 We could go through and round. 169 00:09:46,050 --> 00:09:47,790 Average is kind of gross. 170 00:09:48,870 --> 00:09:52,320 But if we do that, that's going to get quite gross, too. 171 00:09:52,560 --> 00:09:55,670 And we'll want to break that onto separate lines. 172 00:09:55,680 --> 00:09:59,400 We'd want to format that to make it a lot more legible. 173 00:10:00,030 --> 00:10:02,040 But otherwise, it looks good. 174 00:10:02,610 --> 00:10:08,940 So the last thing I'll do is optional, is just to show you, if we wanted to add in a third status 175 00:10:08,940 --> 00:10:16,530 from active, inactive and crazy active or something like that, we can replace this if we go back to 176 00:10:16,530 --> 00:10:17,160 our case. 177 00:10:17,160 --> 00:10:27,630 So we'll do case end as status and we'll just do case when count rating is greater than or equal to 178 00:10:27,630 --> 00:10:28,170 one. 179 00:10:28,800 --> 00:10:35,550 And actually, let's start with if it's greater than or equal to ten, in that case, we'll say power 180 00:10:35,550 --> 00:10:36,180 user. 181 00:10:37,350 --> 00:10:49,140 And then the next thing is when count is greater than or equal to zero, we'll just say active else 182 00:10:49,140 --> 00:10:55,170 and I need my then always forget those else inactive. 183 00:10:56,040 --> 00:11:05,640 So the way I formatted that is basically to say when it's the way that I formatted, that is to say 184 00:11:05,640 --> 00:11:08,160 first, is it greater than or equal to ten? 185 00:11:08,880 --> 00:11:10,590 Then it's a power user and we're done. 186 00:11:11,100 --> 00:11:13,830 But if it's not, that means that it's less than ten. 187 00:11:14,220 --> 00:11:18,030 So if it's less than ten, but still greater than or equal to zero, then they're active. 188 00:11:18,030 --> 00:11:21,330 And that should actually be greater than zero, not equal. 189 00:11:21,330 --> 00:11:27,150 And then if it's not greater than zero and not greater than or equal to ten, that means they're inactive. 190 00:11:27,690 --> 00:11:30,810 So now copy that, paste it over. 191 00:11:30,810 --> 00:11:36,780 And you can see we have two power users, Domingo and Colt, who have count of ten, and now we're done. 192 00:11:37,290 --> 00:11:40,290 So we made it through that quite a bit there. 193 00:11:40,800 --> 00:11:49,560 I will leave this example, but I will also duplicate it and go back to having that simple if statement. 194 00:11:49,560 --> 00:11:50,880 So we don't have to have the case. 195 00:11:51,420 --> 00:11:52,050 So I'll do that. 196 00:11:52,050 --> 00:11:56,970 You don't have to watch that, but it's in the solution text or the solution file if you want to take 197 00:11:56,970 --> 00:11:57,420 a look.