Question: 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.

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

Answer: 
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.