How can we join common tables in MySQL?

Question

I have a complex query in Postgres which I am trying to convert in MySQL. The Postgres query has three chained queries. The first two create two common tables and the final query does a join on these two common tables. A simplified version of the query looks something like this . Is there a way to join these two common tables in MySQL ? I need the query to run for 5.6,5.7 and 8.0 so the new feature for CTE in 8.0 is not a solution.

(Select table1.y_id as year_id, 
       SUM(table1.total_weight) AS metric_value
       from (SELECT student_name,y_id,total_weight from student_metrics where y_id>10 ) table1
       group by year_id
       order by metric_value DESC
       limit by 5
 )table2

The third query should do a join of table1 and table2 on table1.y_id = table2.year_id.

To give a high level idea of what each of the query does:

1. Query 1 fetches data from the master table(Say, table 1) and stores it in a common table based on some conditions
2. Query 2 groups and sorts the rows obtained in Query 1 based on a user specified column and limits it to top 'N'
3. Query 3 returns all details(on table 1) of only these N unique Ids(obtained from table 2) by perfoming a join on table1.id =
   table2.id

Answer 1

You could simply repeat the table1 subquery:

select
    table1.*
from
    (select student_name,y_id,total_weight from student_metrics where y_id>10) as table1
    inner join (
        select tbl1.y_id as year_id, 
        sum(tbl1.total_weight) as metric_value
        from
            (select student_name,y_id,total_weight from student_metrics where y_id>10 ) as tbl1
       group by tbl1.y_id
       order by sum(tbl1.total_weight) desc
       limit by 5
       ) as table2 on table1.y_id = table2.year_id;