Medium genre per movie in SQL Server 2012 genre
Having stumbled upon this problem, I couldn't find a solution either, I need help or point me in the right direction.
The tables classify several films into several genres, for example: Toy Story - in genres; Children's, comedy and animation.
I'm trying to figure out which genres are the clearest and which are so confusing that they tend to be listed among several others for a typical film.
For each genre in the dataset, how many genres are movies in that genre, on average. For example, an Action movie is in 1.3 genres and the average Adventure movie is in 2.9 genres, so Action is a more specific genre.
Below are examples of tables, but here is a sql editor with tables and values in them for reference: https://blazerme.herokuapp.com/queries/new
3 tables:
genre table:
id name
1 Action
2 Adventure
3 Animation
4 Children's
5 Comedy
genre_movies table:
id movie_id genre_id
1 1(Toy Story) 3(Animation)
2 1(Toy Story) 4(Children's)
3 1(Toy Story) 5(Comedy)
4 2(GoldenEye) 1(Action)
5 2(GoldenEye) 2(Adventure)
6 2(GoldenEye) 16(Thriller)
movies table:
id title release_date
1 Toy Story (1995) 1995-01-01
2 GoldenEye (1995) 1995-01-01
3 Four Rooms (1995) 1995-01-01
My best effort so far is trying to find the genre_id count. See below:
SELECT
name, AVG(c.count)
FROM
(SELECT
g.name AS name, COUNT(gm.genre_id) AS count
FROM
genres g
INNER JOIN
genres_movies gm ON g.id = gm.genre_id
INNER JOIN
movies m ON m.id = gm.movie_id
GROUP BY
g.name) c
GROUP BY
name
But that just gives me a count of each genre from the table genres_movies
. I can't figure out how to turn this into an average of genres for movies in each genre.
source to share
I solve problems like this by first creating the innermost query with the basic information I want and then working outward.
Essential information required:
select movie_id, count(*) from genres_movies group by movie_id
Full request:
select G.name, Round(avg(numgenres),2) as MuddleFactor
from genres_movies GM
inner join
(select movie_id, count(*) as numGenres
from genres_movies
group by movie_id) MNG
on MNG.movie_id = GM.movie_id
inner join genres G
on GM.genre_id = G.id
group by G.name
order by MuddleFactor desc
source to share