The Movie Database Solution: Most Movies

[00:00:00]
>> What category is associated with the most movie? So this is kinda flipping the question on its head, right. So we're gonna say SELECT. C.name, COUNT MK.category_ id, there's a bunch of things you could account at that was just gonna make the most sense to me as count.

[00:00:22]
So we actually don't even have to join the movies here, right, because we don't actually need the movie names at all. We just need to know how many connections that have, which movie keywords is more than enough to tell us that. So we're gonna say from movie_keywords mk ,INNER JOIN, categories c.

[00:00:52]
ON, c.id = mk.category_id. GROUP BY. C.name, mk.category_id. ORDER BY, again count will never be NULL, so we don't have to worry about NULLS last or anything like that. Doesn't hurt to put it here. Probably incurs a little bit of extra work on postgreSQL part, but doesn't actually really matter.

[00:01:36]
LIMIT 10 or something like that. All right, so let's take a look and see if we get back, and he gets some, I think most these are German because most of the keywords I checked were either in English or in German. Unsurprising, Falling in Love associated with basically every movie ever.

[00:02:00]
>> It's on the list twice?
>> Yeah, it is, isn't it? I would bet you is cuz they're in different languages. So you could put a WHERE in here. And thus we go here WHERE, and they gotta remember categories has a relationship or not sorry, not the categories.

[00:02:30]
Name, route_id, parent_id, so you might have to. Describe movie keywords. Okay, one of these tables you can eventually join to that will tell you what language something is in, right. And then you can limit it down to like English and I started writing the query and it took like a couple more hops.

[00:02:52]
I was like I don't wanna make people write this because I don't wanna write it and so we stopped here. So, for now, it's sufficient to say that we have a bunch of German keywords in here that's fine. But all those father son relationship movies such as, great, or killer movies.

[00:03:16]
So, a lot of killer movies. Okay, any questions about this query?
>> No but I will say that I messed up at first because I didn't put the GROUP BY on the mk category_id, and that put Falling in Love together.
>> See this is a reason why you'd learn to do that.

[00:03:39]
>> So, pitfall.
>> But let's try.
>> The first keyword on that list means assassination or murder.
>> Nice.
>> So there's more, we're killing the [INAUDIBLE].
>> More killing. Nice, except not nice. Let's clear every here, here we go. That's why it's useful to BROUP BY, because you can see her Fall in Love obviously jumps way up here.

[00:04:07]
Because you probably have a bunch of movies that have Falling in Love English, Falling in Love French or something like that or Falling in Love German, it's probably German if being honest. As combining those into one, which makes Falling in Love looks like there's a lot more of it, right.

[00:04:25]
But it's probably double counting a bunch of those connections. That's a guess, by the way, I don't actually know [LAUGH]. There's ways you could read that query to exclude it where it's pointing to the same movie as well. What do you think, how is this, was this a useful exercise?

[00:04:45]
Stretch your brain a little bit to figure out how to write some queries. This is a honestly my favorite part about writing queries is like, having a question and then using SQL to solve it. That feedback cycle is like the most fun part about data for me personally.