How to write an SQL query using a group by


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:
movies(id: int, name: string, years: int, rank:
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 = 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
    (select m.YEAR, count(m.year) as c, genre
    from movies_genres mg
         inner join movies m
             on = mg.movie_id
    group by genre, m.year)
group by genre, year