I have this table:
actors(id: int, first_name: string, last_name: string, gender: string) directors(id: int, first_name: string, last_name: string) directors genres(director id: int, genre: string, prob: float) movies(id: int, name: string, years: int, rank: float,) movies directors(director id: int, movie id: int) movies genres(movie id: int, genre: string) roles(actor id: int, movie id: int, role: string)
I want to find the year for each genre in which maximum movies for that genre were made.
I am doing the following but I'm stuck, please help!
select m.YEAR, count(m.year) as c, genre from movies_genres, movies m where m.id = movies_genres.movie_id group by genre, m.year;
you are getting the count of the movies for each genre for each year which is great. Now you just need to select the max of those by placing your query as a derived table.
select genre, year, max(c) mc from (select m.YEAR, count(m.year) as c, genre from movies_genres mg inner join movies m on m.id = mg.movie_id group by genre, m.year) group by genre, year