WEBVTT 00:00.280 --> 00:07.650 OK so next up we have a return to these long annoying tables with quite a few fields on this one. 00:07.650 --> 00:13.530 This is sort of like analytics for our reviewers and what we're doing here is compiling statistics on 00:13.530 --> 00:13.860 them. 00:13.860 --> 00:17.490 So Thomas Stoneman how many reviews has he written. 00:17.490 --> 00:18.020 Five. 00:18.090 --> 00:20.940 OK what's the minimum he's assigned. 00:20.950 --> 00:24.120 7:04 No the maximum review is a nine point five. 00:24.180 --> 00:31.220 His average is eight point two and then a status which is saying that he's active and that's determined 00:31.220 --> 00:34.330 to based off if the user has any reviews. 00:34.530 --> 00:38.390 So Marlatt and craft Craford has zero. 00:38.580 --> 00:41.810 So he is inactive but everyone else is active. 00:41.820 --> 00:47.730 So we've got a lot of pieces here and probably the most crucial thing to point out is that we have Marlan 00:48.180 --> 00:53.060 Craford who has zero reviews which we need to pay attention to. 00:53.100 --> 00:57.360 We have to pay special attention to that because there's not going to be a natural join if we do an 00:57.360 --> 01:04.290 inner join to we'll need to do a left join which will highlight the fact that he has no reviews and 01:04.290 --> 01:05.900 then the rest is not too difficult. 01:05.910 --> 01:11.180 Count min max average should be pretty comfortable with that status. 01:11.250 --> 01:12.680 We need to do some logic there. 01:13.050 --> 01:16.760 So let's start by adding in challenge 01:19.740 --> 01:26.180 6 we'll just call this reviewer stacks. 01:26.700 --> 01:27.040 OK. 01:27.060 --> 01:31.470 So the tables we're working with we've got the reviewer table which is where we're pulling things like 01:31.470 --> 01:32.700 the name. 01:32.700 --> 01:36.350 And then we've got the reviews table which is where we're pulling count. 01:36.600 --> 01:39.990 Well that's where we're pulling the the actual ratings themselves. 01:39.990 --> 01:43.070 And then we're basically getting this data from there. 01:43.470 --> 01:46.850 So we don't need to worry about the series table we're not working with that. 01:47.100 --> 01:48.400 We will be in the next. 01:48.450 --> 01:49.470 The final challenge here. 01:49.500 --> 01:50.660 We're going to put all three together. 01:50.670 --> 01:52.970 But in this case we're not. 01:53.040 --> 01:55.690 So we'll start with a select star. 01:55.830 --> 01:56.620 Let's just do. 01:56.730 --> 02:04.790 Well we'll start to start it's like star from reviewers we get all of our viewers here. 02:04.830 --> 02:05.740 Perfect. 02:05.880 --> 02:07.350 We want all of them. 02:07.590 --> 02:11.740 So that will be the left part of our joint if you remember the two circles a and b. 02:11.760 --> 02:12.930 This will be Circle A. 02:12.960 --> 02:13.350 Right here. 02:13.350 --> 02:18.350 We want all of this to show up even though Marlyn doesn't have any reviews. 02:18.420 --> 02:30.040 Then we're going to do a join join revues on reviewers dot ID's where the reviewer Id like this one 02:30.040 --> 02:38.280 two three four five six is equal to the reviews dot reviewer ID. 02:38.580 --> 02:45.330 And then in the case of Marlan there is no reviewer ID equal to 7 in the reviews table. 02:45.360 --> 02:46.190 So we'll get. 02:46.270 --> 02:47.310 No no no. 02:47.490 --> 02:56.280 And so on OK let's try it and if we take a look at the results here we don't get those Nil's and that's 02:56.280 --> 02:59.670 because we didn't specify that it's a left join. 02:59.670 --> 03:01.560 We left that off. 03:01.590 --> 03:04.810 Now at the end we have Marlan Craford. 03:04.830 --> 03:06.340 No no no no. 03:06.630 --> 03:15.750 We need to make use of to do things like this where we have zero min max average or 0 0 and then inactive. 03:16.110 --> 03:21.710 So we'll have to kind of make special exceptions for Marlin or for anybody with no. 03:22.250 --> 03:24.910 OK so now let's start whittling down what we need. 03:25.050 --> 03:29.190 We on first name last name. 03:30.210 --> 03:31.070 So what we have here. 03:31.110 --> 03:32.000 Right. 03:32.460 --> 03:34.560 Then we want things that count. 03:34.560 --> 03:41.170 Min max and average which requires us to group by but let's start by just putting rating here without 03:41.190 --> 03:42.450 grouping. 03:43.440 --> 03:46.120 Just verify what we need exactly. 03:46.940 --> 03:52.010 OK so here's all the reviews and we want to group them together and we could group based off of first 03:52.010 --> 03:53.340 name and last name. 03:53.570 --> 03:55.820 But that's not guaranteed to be unique of course. 03:55.850 --> 04:04.230 So it's better to group based off of the reviewer's I.D. so we can do that now group by reviewers. 04:04.410 --> 04:06.140 Id just like that. 04:06.460 --> 04:07.470 And if we do that. 04:07.470 --> 04:14.790 Whoops didn't mean to expand again our group is working into weird magic. 04:15.160 --> 04:16.670 It's working behind the scenes. 04:16.720 --> 04:19.320 So now we can do things like average. 04:19.330 --> 04:21.280 For instance let's start there. 04:22.030 --> 04:24.830 Well actually even easier is counts. 04:25.190 --> 04:27.030 We'll just count the ratings. 04:27.670 --> 04:29.330 So behind the scenes it's group them. 04:29.330 --> 04:31.930 Remember it went from this here. 04:31.960 --> 04:37.390 So if we take a look at like these they were all grouped together. 04:37.480 --> 04:44.570 So under Thomas Stoneman there's one two three four five but we only see Thomas Stoneman. 04:44.620 --> 04:48.940 We don't get five results but they're there behind the scenes. 04:48.940 --> 04:55.650 So you do count rading and you can see it looks good. 04:55.650 --> 04:57.500 We get 5 9. 04:57.500 --> 05:01.020 You can see Domingo and called for a ton of reviews. 05:01.260 --> 05:03.420 Poor Marlon just hasn't written any. 05:03.420 --> 05:05.280 So that's a start. 05:05.310 --> 05:07.620 Then you've got Min and Max. 05:07.620 --> 05:17.360 So those were quite simple to just let's start with men the men reading they've written and that works 05:17.360 --> 05:24.500 for everybody except for good old Marlon causing us problems and we should be relatively comfortable 05:24.500 --> 05:26.560 with fixing this at this point. 05:26.570 --> 05:31.010 Notice that I just sent it to zero could have said it to be anything you could leave it at all. 05:31.010 --> 05:41.020 But let's make it zero to match the chart so we can use our if no min rating then we'll set it to be 05:41.080 --> 05:41.990 zero. 05:42.580 --> 05:47.920 And if I leave it like this our table is going to be kind of ugly because we have this if not appear 05:47.980 --> 05:49.290 we don't need all the space. 05:49.390 --> 06:00.300 So let's call this one as count and this one as men that you put down on the space looking better. 06:00.730 --> 06:08.320 And then the same thing for max which we can just copy and just change men to Max. 06:08.360 --> 06:15.120 OK so we got men and Max that's just triple check. 06:15.150 --> 06:16.080 There we go. 06:16.170 --> 06:18.210 Count min max. 06:18.250 --> 06:23.590 And we've got average which by now you should be comfortable with doing as well. 06:23.720 --> 06:32.000 It's the same thing as average but we'll run into that same problem with no we don't want no there. 06:32.040 --> 06:33.150 We just want zero. 06:33.720 --> 06:35.940 So if no. 06:35.940 --> 06:40.290 Once again just like that 06:44.310 --> 06:46.410 or copy it. 06:47.120 --> 06:47.980 OK. 06:48.110 --> 06:54.550 And we've got the first almost everything except now we need to work with status and status. 06:54.580 --> 06:57.290 It's a little different because there's some logic here. 06:57.410 --> 06:58.550 And what's that based off of. 06:58.630 --> 07:00.030 Well it's based off of count. 07:00.170 --> 07:06.080 So if count is more than one or greater than or equal to one then we'll call that user active for the 07:06.080 --> 07:13.190 reviewer active otherwise it's inactive or we could switch it around we could say if count is equal 07:13.190 --> 07:16.850 to exactly zero then status is inactive. 07:16.850 --> 07:18.820 Otherwise they're active. 07:19.400 --> 07:24.280 And to do it we could use a case statement and we can start with that. 07:24.410 --> 07:26.640 What we're working with again is count. 07:27.050 --> 07:30.300 So let's do this at the bottom our case. 07:30.430 --> 07:31.380 And I'd like to do. 07:31.460 --> 07:37.050 And as I said we'll call this status Okay. 07:37.400 --> 07:44.780 And our first thing will check for in case is when Count rating and we can do when it's greater than 07:44.780 --> 07:58.330 or equal to 1 then we say active status will be active else we need then here. 07:58.810 --> 08:05.190 Otherwise in active active inactive. 08:05.690 --> 08:10.450 Okay let's try that okay. 08:10.520 --> 08:11.650 It works just fine. 08:12.090 --> 08:16.350 But I did say there's another thing that we can do which I also haven't shown you until now I don't 08:16.350 --> 08:21.360 want to overload you with a bunch of different ways of doing logic but for simpler case statements we 08:21.360 --> 08:25.830 can use another function or another logical function which is called. 08:25.890 --> 08:29.310 If so I'm just going to replace this with if I'll show you what it looks like. 08:29.420 --> 08:36.420 We just say if and then the condition that we're looking for so if Count rating is greater than or equal 08:36.420 --> 08:43.770 to 1 then the second argument is what we want to spit out and then the third one is the else condition 08:43.860 --> 08:45.800 or the else argument. 08:46.020 --> 08:48.970 So these are equivalent right here. 08:49.110 --> 08:51.160 As long as they put status. 08:51.160 --> 08:57.220 So let me make that a little bit easier to see this right here is saying OK if Count rating. 08:57.300 --> 09:04.200 So if the count of all the ratings per each user each reviewer is greater than or equal to one then 09:05.040 --> 09:06.360 status is active. 09:06.450 --> 09:09.340 If that's not true status is inactive. 09:09.780 --> 09:14.150 So this works great when we only have these two things that we're working with right. 09:14.170 --> 09:15.350 Active and inactive. 09:15.570 --> 09:21.870 But if we wanted to add another thing in it's a little more complex like if we wanted to say OK if it's 09:21.870 --> 09:23.270 greater than 1 then they're active. 09:23.280 --> 09:29.250 And if it's greater than 10 or greater than equal to 10 they're a power user or something otherwise 09:29.250 --> 09:30.200 they're inactive. 09:30.360 --> 09:33.720 It's much easier to use a case statement for that. 09:33.720 --> 09:39.900 So I can show you that but I'm going to get rid of this first and just make sure that our new edition 09:39.900 --> 09:42.260 works the same way. 09:43.310 --> 09:44.480 Looks good to me. 09:44.480 --> 09:46.000 We could go through and round. 09:46.130 --> 09:54.350 Average is kind of gross but if we do that that's going to get quite gross to want to break that on 09:54.350 --> 09:55.790 two separate lines. 09:55.790 --> 10:02.750 We do want to format that to make it a lot more legible but otherwise it looks good. 10:02.750 --> 10:09.200 So the last thing I'll do is optional as just to show you if we wanted to add in a third status from 10:09.650 --> 10:14.650 active inactive and crazy active or something like that we can replace this. 10:14.650 --> 10:26.870 If I go back to our case with the case and as status and we'll just do case when Count rating is greater 10:26.870 --> 10:32.430 than or equal to 1 and actually let's start with if it's greater than or equal to 10. 10:32.690 --> 10:36.970 In that case we'll say power user. 10:37.460 --> 10:44.010 And then the next thing is when Count is greater than or equal to zero. 10:44.480 --> 10:50.540 Let's just say active else and I need my hands. 10:50.860 --> 10:55.580 I always forget those Else inactive. 10:55.590 --> 11:06.130 So the way I formatted that is basically to say when it's the way that I formatted that is to say first 11:06.430 --> 11:08.890 OK is it greater than or equal to 10. 11:08.990 --> 11:10.780 Then it's a power user and we're done. 11:11.240 --> 11:13.970 But if it's not that means that it's less than 10. 11:14.360 --> 11:18.470 So if it's less than 10 but still greater than or equal to zero then they're active and that should 11:18.470 --> 11:21.330 actually be greater than zero not equal. 11:21.530 --> 11:27.720 And then if it's not greater than zero if not greater than or equal to 10 that means they're inactive. 11:27.770 --> 11:34.550 So now copy that pasted over and you can see we have two power users Domingo and colt who have a count 11:34.550 --> 11:35.410 of 10. 11:35.900 --> 11:36.990 And now we're done. 11:37.430 --> 11:40.620 So we made it through that quite a bit there. 11:40.880 --> 11:43.310 I will leave this example. 11:43.960 --> 11:50.130 But I will also duplicate it and go back to having that simple if statement so we don't have to have 11:50.130 --> 11:50.920 a case. 11:51.170 --> 11:52.040 OK so I'll do that. 11:52.040 --> 11:56.190 You don't have to watch that but it's in the solution text or the solution file. 11:56.210 --> 11:57.210 If you want to take a look.