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;

```
Did you try something like this ?

```
select
  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;

```