37. Most popular movie category

easy

Instruction

  • Return the name of the category that has the most films.
  • If there are ties, return just one of them.

Table 1: category

Movie categories.

  col_name   | col_type
-------------+--------------------------
 category_id | integer
 name        | text

Table 2: film_category

A film can only belong to one category

  col_name   | col_type
-------------+--------------------------
 film_id     | smallint
 category_id | smallint

Sample results

  name
--------
Category Name

Expected results

Solution postgres

SELECT 
	C.name
FROM film_category FC
INNER JOIN category C
ON C.category_id = FC.category_id
GROUP BY C.name
ORDER BY COUNT(*) DESC
LIMIT 1;
    

Explanation

This query selects the name of the category that appears the most frequently in the film_category table. It does this by joining the film_category table with the category table on their category_id columns. Then, it groups the result by the name of the category and orders the groups by the count of rows in each group in descending order. Finally, it limits the result to only the first row, which will be the category with the highest count.



More INNER JOIN questions

ID Title Level FTPR
179 Members who ever moved from Microsoft to Google linkedin medium
35%
162 Number of orders per brand walmart easy
40%
150 Countries with above average customers social easy
10%
99 Page recommendation social medium
12%
91 same day friend request acceptance rate spotify easy
11%
50 Top 5 cities for movie rentals easy
23%
49 Top 3 money making movie categories medium
19%
48 Movie and TV actors easy
43%
40 Top 2 most rented movie in June 2020 medium
15%
39 Most productive actor with inner join easy
23%
38 Most popular movie category (name and id) medium
18%
36 Actors from film 'AFRICAN EGG' easy
36%