SQL: Film Category vs. City - Write a query to find the number of occurrences of each film_category in each city.

Film Category vs. City

Description: Write a query to find the number of occurrences of each film_category in each city. Arrange them in the decreasing order of their category count.

Sample Output

Hi,
You can use this query to get the count of each film category in each city.

 SELECT name, city, COUNT(category_id) AS category_count
 FROM category
 INNER JOIN film_category
 USING (category_id)
 INNER JOIN inventory
 USING(film_id)
 INNER JOIN store
 USING (store_id)
 INNER JOIN address
 USING (address_id)
 INNER JOIN city
 USING (city_id)
 GROUP BY name, city
 ORDER BY category_count DESC;
  • We get the count of category_id from the category table to get the occurrences.
  •  We use inner join with a series of related tables to a couple of associated information to get the needed result.