38. Most popular movie category (name and id)

medium

***********

  • **************************************************************************************
  • *******************************************

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

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.

Expected results



More INNER JOIN questions

ID Title Level FTPR
213 Interchange revenue by issuer and merchant category visa easy
70%
179 Members who ever moved from Microsoft to Google linkedin medium
31%
162 Number of orders per brand walmart easy
41%
150 Countries with above average customers google easy
15%
99 Page recommendation facebook medium
13%
91 same day friend request acceptance rate spotify easy
13%
50 Top 5 cities for movie rentals easy
22%
49 Top 3 money making movie categories medium
18%
48 Movie and TV actors easy
38%
40 Top 2 most rented movie in June 2020 medium
14%
39 Most productive actor with inner join easy
22%
37 Most popular movie category easy
15%
36 Actors from film 'AFRICAN EGG' easy
32%