Instruction
- Write a query to return the film category id with the most films, as well as the number films in that category.
Table: film_category
A film can only belong to one category
col_name | col_type -------------+-------------------------- film_id | smallint category_id | smallint
Sample results
category_id | film_cnt
------------+----------
1 | 2
Expected results
Solution postgres
SELECT
category_id,
COUNT(*) film_cnt
FROM film_category
GROUP BY category_id
ORDER BY film_cnt DESC
LIMIT 1;
Explanation
This query is selecting the category_id column and counting the number of films in each category by grouping the results by category_id using the film_category table. The results will be ordered in descending order by the film count (film_cnt) and only the top result will be returned due to the LIMIT 1 clause. This query is useful for finding the category with the most films in the database.
Copied
Your results