OK so next up we have a return to these long annoying tables with quite a few fields on this one.
This is sort of like analytics for our reviewers and what we're doing here is compiling statistics on
them.
So Thomas Stoneman how many reviews has he written.
Five.
OK what's the minimum he's assigned.
7:04 No the maximum review is a nine point five.
His average is eight point two and then a status which is saying that he's active and that's determined
to based off if the user has any reviews.
So Marlatt and craft Craford has zero.
So he is inactive but everyone else is active.
So we've got a lot of pieces here and probably the most crucial thing to point out is that we have Marlan
Craford who has zero reviews which we need to pay attention to.
We have to pay special attention to that because there's not going to be a natural join if we do an
inner join to we'll need to do a left join which will highlight the fact that he has no reviews and
then the rest is not too difficult.
Count min max average should be pretty comfortable with that status.
We need to do some logic there.
So let's start by adding in challenge
6 we'll just call this reviewer stacks.
OK.
So the tables we're working with we've got the reviewer table which is where we're pulling things like
the name.
And then we've got the reviews table which is where we're pulling count.
Well that's where we're pulling the the actual ratings themselves.
And then we're basically getting this data from there.
So we don't need to worry about the series table we're not working with that.
We will be in the next.
The final challenge here.
We're going to put all three together.
But in this case we're not.
So we'll start with a select star.
Let's just do.
Well we'll start to start it's like star from reviewers we get all of our viewers here.
Perfect.
We want all of them.
So that will be the left part of our joint if you remember the two circles a and b.
This will be Circle A.
Right here.
We want all of this to show up even though Marlyn doesn't have any reviews.
Then we're going to do a join join revues on reviewers dot ID's where the reviewer Id like this one
two three four five six is equal to the reviews dot reviewer ID.
And then in the case of Marlan there is no reviewer ID equal to 7 in the reviews table.
So we'll get.
No no no.
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
because we didn't specify that it's a left join.
We left that off.
Now at the end we have Marlan Craford.
No no no no.
We need to make use of to do things like this where we have zero min max average or 0 0 and then inactive.
So we'll have to kind of make special exceptions for Marlin or for anybody with no.
OK so now let's start whittling down what we need.
We on first name last name.
So what we have here.
Right.
Then we want things that count.
Min max and average which requires us to group by but let's start by just putting rating here without
grouping.
Just verify what we need exactly.
OK so here's all the reviews and we want to group them together and we could group based off of first
name and last name.
But that's not guaranteed to be unique of course.
So it's better to group based off of the reviewer's I.D. so we can do that now group by reviewers.
Id just like that.
And if we do that.
Whoops didn't mean to expand again our group is working into weird magic.
It's working behind the scenes.
So now we can do things like average.
For instance let's start there.
Well actually even easier is counts.
We'll just count the ratings.
So behind the scenes it's group them.
Remember it went from this here.
So if we take a look at like these they were all grouped together.
So under Thomas Stoneman there's one two three four five but we only see Thomas Stoneman.
We don't get five results but they're there behind the scenes.
So you do count rading and you can see it looks good.
We get 5 9.
You can see Domingo and called for a ton of reviews.
Poor Marlon just hasn't written any.
So that's a start.
Then you've got Min and Max.
So those were quite simple to just let's start with men the men reading they've written and that works
for everybody except for good old Marlon causing us problems and we should be relatively comfortable
with fixing this at this point.
Notice that I just sent it to zero could have said it to be anything you could leave it at all.
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
zero.
And if I leave it like this our table is going to be kind of ugly because we have this if not appear
we don't need all the space.
So let's call this one as count and this one as men that you put down on the space looking better.
And then the same thing for max which we can just copy and just change men to Max.
OK so we got men and Max that's just triple check.
There we go.
Count min max.
And we've got average which by now you should be comfortable with doing as well.
It's the same thing as average but we'll run into that same problem with no we don't want no there.
We just want zero.
So if no.
Once again just like that
or copy it.
OK.
And we've got the first almost everything except now we need to work with status and status.
It's a little different because there's some logic here.
And what's that based off of.
Well it's based off of count.
So if count is more than one or greater than or equal to one then we'll call that user active for the
reviewer active otherwise it's inactive or we could switch it around we could say if count is equal
to exactly zero then status is inactive.
Otherwise they're active.
And to do it we could use a case statement and we can start with that.
What we're working with again is count.
So let's do this at the bottom our case.
And I'd like to do.
And as I said we'll call this status Okay.
And our first thing will check for in case is when Count rating and we can do when it's greater than
or equal to 1 then we say active status will be active else we need then here.
Otherwise in active active inactive.
Okay let's try that okay.
It works just fine.
But I did say there's another thing that we can do which I also haven't shown you until now I don't
want to overload you with a bunch of different ways of doing logic but for simpler case statements we
can use another function or another logical function which is called.
If so I'm just going to replace this with if I'll show you what it looks like.
We just say if and then the condition that we're looking for so if Count rating is greater than or equal
to 1 then the second argument is what we want to spit out and then the third one is the else condition
or the else argument.
So these are equivalent right here.
As long as they put status.
So let me make that a little bit easier to see this right here is saying OK if Count rating.
So if the count of all the ratings per each user each reviewer is greater than or equal to one then
status is active.
If that's not true status is inactive.
So this works great when we only have these two things that we're working with right.
Active and inactive.
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
greater than 1 then they're active.
And if it's greater than 10 or greater than equal to 10 they're a power user or something otherwise
they're inactive.
It's much easier to use a case statement for that.
So I can show you that but I'm going to get rid of this first and just make sure that our new edition
works the same way.
Looks good to me.
We could go through and round.
Average is kind of gross but if we do that that's going to get quite gross to want to break that on
two separate lines.
We do want to format that to make it a lot more legible but otherwise it looks good.
So the last thing I'll do is optional as just to show you if we wanted to add in a third status from
active inactive and crazy active or something like that we can replace this.
If I go back to our case with the case and as status and we'll just do case when Count rating is greater
than or equal to 1 and actually let's start with if it's greater than or equal to 10.
In that case we'll say power user.
And then the next thing is when Count is greater than or equal to zero.
Let's just say active else and I need my hands.
I always forget those Else inactive.
So the way I formatted that is basically to say when it's the way that I formatted that is to say first
OK is it greater than or equal to 10.
Then it's a power user and we're done.
But if it's not that means that it's less than 10.
So if it's less than 10 but still greater than or equal to zero then they're active and that should
actually be greater than zero not equal.
And then if it's not greater than zero if not greater than or equal to 10 that means they're inactive.
So now copy that pasted over and you can see we have two power users Domingo and colt who have a count
of 10.
And now we're done.
So we made it through that quite a bit there.
I will leave this example.
But I will also duplicate it and go back to having that simple if statement so we don't have to have
a case.
OK so I'll do that.
You don't have to watch that but it's in the solution text or the solution file.
If you want to take a look.