mysql - how can i do this sql request?

Question

As part of a personal project I'm developing a netflix like. So I have a database with movies sorted by genre, The user can like the movies so they can be added to favorites.
I would like to make a query that would send me a list of the genres with the most movies in favorites. At the moment I can only display the number of bookmarked movies by genre with this command.

select count(*), genres.nom
from favoris
left join films on films.ID_film = favoris.ID_film
left join genres on genres.ID_genre = films.ID_genre
group by genres.nom;

Here is how my database is made: db screenshot

Have you any idea please ?

question from:https://stackoverflow.com/questions/65873218/how-can-i-do-this-sql-request

Answer 1

So you would join favoris table and films table and genre table and after that you can just find the user count based on the film genre, below is the query for that

SELECT 
    gn.ID_genre,
    gn.nom,
    COUNT(fv.ID_user) AS user_favorite_count
FROM favoris AS fv
    INNER JOIN films AS flm ON fv.ID_film = flm.ID_film 
    INNER JOIN genres AS gn ON gn.ID_genre = flm.ID_genre
GROUP BY gn.ID_genre, gn.nom
ORDER BY COUNT(fv.ID_user) DESC;