Okay.
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 written?
Five.
What's the minimum?
He's assigned 7.0.
The maximum review is a 9.5.
His average is 8.02.
And then a status which is saying that he's active and that's determined based off of the if the user
has any reviews.
So Marlon Crawford 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 Marlon Crawford, 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.
So 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.
Six.
We'll just call this reviewer stats.
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 Min Well, it'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 do star to start.
It's like star from reviewers.
We get all of our reviewers here.
Perfect.
We want all of them.
So that will be the left part of our joint.
If we remember the two circles, A and B, this will be circle A right here.
We want all of this to show up.
Even though Marlin doesn't have any reviews.
Then we're going to do a join join reviews on reviewers dot id.
So wear 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 Marlin, there is no reviewer ID equal to seven in the reviews table, so we'll
get null, null, null and so on.
Okay.
Let's try it.
And if we take a look at the results here, we don't get those NOLs.
And that's because we didn't specify that it's a left join.
We left that off.
And now at the end, we have Marlon Crawford.
No, no, no, no.
Which will need to make use of to do things like this where we have zero min max average or all zero
and then inactive.
So we'll have to kind of make special exceptions for Marlon or for anybody with NULL.
Okay.
So now let's start whittling down what we need.
We want first name.
We want last name.
So what we have here right then we want things like count min, max and average which require us to
group by.
But let's start by just putting rating here without grouping and just verify what we need.
Exactly.
Okay, 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 ID so we can do that now group by reviewer's ID
just like that.
And if we do that, whoops.
Didn't mean to expand that.
Again.
Our group Bye is working its 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 and we'll just count the ratings.
So behind the scenes, it's grouped 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 we'll do count rating.
And you can see it looks good.
We get five nine.
You can see Domingo and Colt have written a ton of reviews and Marlon just hasn't written any, so that's
a start.
Then we've got Min and Max.
So those are quite simple to.
Just let's start with min.
The min rating they've written.
And that works for everybody except for good old marlin causing us problems.
And we should be relatively comfortable with fixing this at this point.
Notice that I just sent it to be zero.
You could have set it to be anything.
You could leave it as null, but let's make it zero to match the chart so we can use our if null.
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 null up
here, we don't need all the space.
So let's call this one as count and this one as min.
That should cut down on the space looking better.
And then the same thing for Max.
We can just copy and just change min to Max.
Okay.
So we got Min and Max.
Let's just triple check.
There we go.
Count Min Max.
Then we 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 NULL.
We don't want null there.
We just want zero.
So if null once again.
Just like that.
We'll copy it.
And we've got first almost everything, except now we need to work with status.
And status is a little different because there's some logic here.
And what's it 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 or 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 end as and we'll call this status.
Okay.
And our first thing we'll check for in case is when count rating and we can do when it's greater than
or equal to one.
Then we'll say active status will be active.
Else?
Can we need then here.
Otherwise in active.
Active and active.
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 didn'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 one, 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 I put status.
So let me make that a little bit easier to see.
This right here is saying, okay, if count rating.
So if the count of all the ratings per each user or 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, okay, if it's greater than one, then they're active.
And if it's greater than ten or greater than equal to ten, 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, too.
And we'll want to break that onto separate lines.
We'd 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, is 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 we go back to
our case.
So we'll do case end as status and we'll just do case when count rating is greater than or equal to
one.
And actually, let's start with if it's greater than or equal to ten, in that case, we'll say power
user.
And then the next thing is when count is greater than or equal to zero, we'll just say active else
and I need my then 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, is it greater than or equal to ten?
Then it's a power user and we're done.
But if it's not, that means that it's less than ten.
So if it's less than ten, 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 and not greater than or equal to ten, that means they're inactive.
So now copy that, paste it over.
And you can see we have two power users, Domingo and Colt, who have count of ten, 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 the case.
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.