How do I make array dynamic in LATERAL VIEW EXPLODE?

I have a bunch of student records in the table.

school  stduent_id   start_date    end_date
111      123         2010-02-03   2012-02-03
222      345         2013-02-03   2014-02-03
222      567         2015-02-03   2018-02-03

I wanted to count how many students each school has each year, say from 2014 all the way to 2021.

Currently, I am using lateral view, but it is hardcoded:

SELECT school
    , yrs
    , COUNT(DISTINCT stduent_id) AS cnt
FROM TABLE
LATERAL VIEW EXPLODE(ARRAY(2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021))a AS yrs
WHERE 1=1
    AND YEAR(start_date) <= yrs
    AND YEAR(end_date) >= yrs

I wanted this to be updated automatically when the year moves, eg, now should be 2014-2021, next year it should be 2015-2022.

Any idea how should I do this? Thank you!

Solution

You can generate year range using posexplode:

with year_range as(--7 years back from current year till current year
select year(current_date)-7+i as yr
from ( select posexplode(split(space(7),' ')) as (i,x) 
) s
)

SELECT school
    , y.yr
    , COUNT(DISTINCT stduent_id) AS cnt
FROM TABLE
     CROSS JOIN year_range y
GROUP BY school, y.yr
WHERE 1=1
    AND YEAR(start_date) <= y.yr
    AND YEAR(end_date) >= y.yr