'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:

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 |
|---|
