The Movie Database Solution: Most Money

[00:00:00]
>> So hopefully all this was super easy, no problems. I'm just getting these were all really hard. So if you struggled with them, I also struggled with them. So, which movie made the most money? This one shouldn't have any joins and epaulets, kinda of just go through my process of how I would figure this out.

[00:00:19]
It's obviously gonna come from the movies table. So let's describe the movies table. We have a revenue, which is numeric. Okay, so, basically we wanna get names and revenues back. And then we just wanna order by revenue. There's a couple of ways we can go about this. So, let's try this, SELECT name, revenue FROM movies.

[00:00:54]
Then here, let's just just naively we'll just gonna do order by revenue. And like LIMIT 5 or 10 or something like that, right? Well, so right now it's ordering in ascending order, which is not useful because we don't want movies that have zero, right? We want movies that actually have revenues, so we're gonna do order by descending.

[00:01:26]
We still have this problem here. What it's actually doing is it's giving us back series and episodes of TV shows cuz they're also mixed in there, right? Those, obviously, don't have a box office revenue, right? So, that doesn't make any sense. So, we need to get the NULL revenues to the end cuz right now it's sorting those as the top, right?

[00:01:49]
We don't want that. Two ways to do that. The way that I have here in my notes, we just use coalesce again. Coalesce revenue, and if there's nothing in revenue, then make it zero. That works, right? Because that makes anything that's NULL 0. You could also do, and this is probably actually more appropriate, NULLS LAST, I think is what it ends up being.

[00:02:19]
Yeah, NULLS LAST, you're just telling it, put the Nulls Last when there's a null there, right? You can also do NULLS FIRST, right? But that's actually the default behavior, so this is just redundant with NULLS FIRST. But just so you know, that is possible. So even though this is not what's in my notes, this is actually probably the more correct one.

[00:02:47]
But I didn't show you that till now, so I'm not expecting you to know that.