How to make this query better in SQL for this repeated query in 'from clause' -

is there way make query better removing repeated pattern of select in clause? best efficient way write query? using sqlite.

select abs(avg(ravg1) - avg(ravg2)) (select avg(stars) ravg1        movie m join            rating r            on m.mid = r.mid        group r.mid       having year < 1980      ),       (select avg(stars) ravg2        movie m join       rating r on m.mid = r.mid        group r.mid       having year > 1980      ) 

if want difference in ratings years before , after 1980, can use conditional aggregation:

  select abs(avg(case when year < 1980 stars end) -              avg(case when year > 1980 stars end)             )   movie m join        rating r        on m.mid = r.mid;