38. Most popular movie category (name and id)

medium

Instruction

  • Write a query to return the name of the most popular film category and its category id
  • 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

 category_id |  name
-------------+--------
          123 | Category

Expected results

Solution postgres

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

Explanation

This PostgreSQL query retrieves the category ID and the maximum name of the category with the highest number of films. The query joins the "film_category" and "category" tables using their category IDs and groups the results by category ID. It then orders the results by the count of films in descending order and limits the output to just one row, which corresponds to the category with the highest number of films.



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%
37 Most popular movie category easy
17%
36 Actors from film 'AFRICAN EGG' easy
36%