Clash Royale CLAN TAG

SQL Query for find the decade with the largest number of records

I have a database including the following tables:

ACTOR (id, fname, lname, gender)
MOVIE (id, name, year, rank)
DIRECTOR (id, fname, lname)
CAST (a_id, m_id, role)
MOVIE_DIRECTOR (d_id, m_id)


Now I want to retrieve the data of the following question.

Ques: "A decade is a sequence of 10 consecutive years. For example, 1965, 1966, ..., 1974 is a decade, and so is 1967, 1968, ..., 1976. Find the decade with the largest number of films"

3 Answers
3

Did you try something like this ?

select
distinct m.year as decade_begin,
count(rollup.id) as movies_count
from
MOVIE m,
MOVIE rollup
where
rollup.year >= decade_begin and rollup.year < decade_begin+10
group by
decade_begin
order by
movies_count;


distinct

movies_count

ORDER BY movies_count DESC LIMIT 1

I would do this by generating the years, joining in the movies, and then aggregating:

select y.year as decade_start, y.year + 9 as decade_end,
count(*) as num_movies
from (select distinct year from movies) y join
movies m
on m.year >= y.year and m.year < y.year + 10
group by y.year
order by count(*) desc
limit 1;


Only the MOVIE table seems to matter here to get that answer.

In MySql 8.x one could use a self-join on a CTE for this.

WITH MOV AS
(
SELECT year as movie_year, COUNT(id) as total_movies
FROM MOVIE
GROUP BY year
)
SELECT
m1.movie_year as decade_start,
MAX(m2.movie_year) as decade_end,
SUM(m2.total_movies) as total_movies
FROM MOV m1
LEFT JOIN MOV m2 ON (m2.movie_year BETWEEN m1.movie_year AND m1.movie_year + 9)
GROUP BY m1.movie_year
ORDER BY SUM(m2.total_movies) DESC
LIMIT 1


In MySql 5.x, which doesn't support CTE's, simply replace the MOV's for m1 and m2 by a sub-query with the query from the CTE.

having

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.