DROP TABLE IF EXISTS Rooms, Classes; 

CREATE TABLE Rooms( 

  room_nbr CHAR(2) NOT NULL PRIMARY KEY, room_size INTEGER NOT NULL 

) ; 

CREATE TABLE Classes( 

  class_nbr CHAR(2) NOT NULL PRIMARY KEY, class_size INTEGER NOT NULL 

) ; 

INSERT INTO Classes   

VALUES ('c1', 80),('c2', 70),('c3', 65),('c4', 55),('c5', 50),('c6', 40); 

INSERT INTO Rooms  

VALUES ('r1', 70),('r2', 40),('r3', 50),('r4', 85),('r5', 30),('r6', 65),('r7', 55); 

WITH  

  Matches AS ( 

    SELECT  

      class_nbr, class_size, room_nbr, room_size,  

      CASE WHEN class_size = room_size THEN 1 ELSE 0 END AS exact_match 

    FROM Classes 

    JOIN Rooms ON class_size <= room_size 

  ), 

  Preferences AS ( 

    SELECT 

      class_nbr, class_size,  

      ROW_NUMBER() OVER ( 

        PARTITION BY class_nbr ORDER BY exact_match, room_size, room_nbr 

      ) AS class_room_pref,

      room_nbr, room_size,  

      ROW_NUMBER() OVER ( 

        PARTITION BY room_nbr ORDER BY exact_match, class_size DESC, class_nbr 

      ) AS room_class_pref

    FROM Matches m 

    WHERE NOT EXISTS (  

      SELECT 1 FROM Matches WHERE room_nbr = m.room_nbr AND class_size > m.class_size 

    ) 

  ), 

  Final AS ( 

    SELECT 

      class_nbr, class_size, room_nbr, room_size, 

      ROW_NUMBER() OVER (

        PARTITION BY class_nbr ORDER BY class_room_pref

      ) AS final_pref

    FROM Preferences p 

    WHERE NOT EXISTS ( 

      SELECT 1 FROM Preferences 

      WHERE room_nbr = p.room_nbr  

        AND class_room_pref = room_class_pref  

        AND  room_class_pref < p.room_class_pref 

    ) 

  ) 

SELECT c.class_nbr, c.class_size, f.room_nbr, f.room_size 

FROM Classes c 

LEFT JOIN Final f ON c.class_nbr = f.class_nbr AND f.final_pref = 1 

ORDER BY 1;