'Getting top 10 most popular within an array column using SQL UNNEST

I am working with a sample data set which gives the following result: enter image description here

Continuing to work, I am now trying to get the top 10 Production Companies (based on "production_companies" field) that made the most number of movies in the most popular genre for a year.

The output Rank | Production Company | Popular Genre | Movie Count

I thought breaking this down to getting the most popular genre for the year would be the 1st step with the following query:

select
    genres.name AS _genre,    
FROM
    commons.movies m,
    UNNEST(m.genres) as genres
WHERE
    SUBSTR(m.release_date, 1, 4) = '2008'
GROUP BY
    genres.name
ORDER BY
    COUNT(genres.name) DESC
LIMIT
    1

I have now go the output as 'Drama' being the most popular genre for the year 2008.

Answering the question to get the most popular prod company and their count has been a bit challenging and failing several times.

I have after several tries got to:

select
    o_prd_cmp.name,
    o_mov.title
from
    commons.movies o_mov,
    unnest(o_mov.genres) as o_gnr,
    UNNEST(o_mov.production_companies) AS o_prd_cmp
where
    SUBSTR(o_mov.release_date, 1, 4) = '2008'
    AND o_gnr.name = (
        select
            genres.name AS _genre,
        FROM
            commons.movies m,
            UNNEST(m.genres) as genres
        WHERE
            SUBSTR(m.release_date, 1, 4) = '2008'
        GROUP BY
            genres.name
        ORDER BY
            COUNT(genres.name) DESC
        LIMIT
            1
    )

Any help with this is greatly appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source