Translate aggregate into SQL function

Just to offer an improved performance expression, so you have something to compare against.

Instead of the buffer and the contains predicate, you can check if the centroid of the geometry of the source feature intersects with other geometries and the area of the source geometry is less or equal than the current geometry one. Also, there is no need to compute ellipsoidal area. And you can build the array just one time:

with_variable(
  'my_array',

  aggregate(
    layer:= 'polygons',
    aggregate:= 'array_agg',
    expression:= "code",
    concatenator:= '-',
    filter:= 
      intersects(
        centroid(geometry(@parent)),
        $geometry)
      AND
      area(geometry(@parent)) <= area($geometry),
    order_by:= -area($geometry), 
  ),

  concat(
    lpad(
      array_get(
        @my_array,
        0),
      3,
      '0'),
    coalesce(
      array_get(
        @my_array,
        1),
      'X'),
    coalesce(
      lpad(
        array_get(
          @my_array,
          2),
        2,
        '0'),
      '00')
  )
)

Maybe the SQL logic and statements are not perfect but it solves the task. Unfortunately till now I was not able to preserve the required order of attributes.

-- calculating the number of geometries that lay inside of the current geometry
-- (upper level)
WITH geom_cont AS (
    SELECT p1.*, SUM(st_contains(p1.geometry, p2.geometry)) - 1 AS num1
    -- "-1" is required to exclude the self-containing
    -- The same result can be also achieved with
    -- SUM(st_contains(p1.geometry, p2.geometry) AND NOT st_equals(p1.geometry, p2.geometry)) AS num1
    FROM polygons AS p1
    CROSS JOIN polygons AS p2 ON st_contains(p1.geometry, p2.geometry)
    GROUP BY p1.fid
),



-- calculating the number of geometries that hold the current geometry
-- (lower level)
geom_with AS (
    SELECT p2.*, SUM(st_within(p2.geometry, p1.geometry)) - 1 AS num2
    -- "-1" is required to exclude the self-within
    -- The same result can be also achieved with
    -- SUM(st_within(p2.geometry, p1.geometry) AND NOT st_equals(p1.geometry, p2.geometry)) AS num2
    FROM polygons AS p2
    CROSS JOIN polygons AS p1 ON st_within(p2.geometry, p1.geometry)
    GROUP BY p2.fid
)

-- main query
SELECT p.*, num1, num2,
    (CASE
        WHEN num1 = 0 AND num2 != 0 THEN GROUP_CONCAT(DISTINCT p3.code)
        WHEN num1 != 0 AND num2 = 0 THEN p3.code
        ELSE GROUP_CONCAT(DISTINCT p4.code)
    END) AS ID
FROM polygons AS p
JOIN geom_cont ON geom_cont.fid = p.fid
JOIN geom_with ON geom_with.fid = p.fid
CROSS JOIN polygons AS p3 ON st_intersects(p.geometry, p3.geometry)
CROSS JOIN polygons AS p4 ON st_contains(p4.geometry, p.geometry)
GROUP BY p.fid

P.S. Works also with single geometries as well as with geometries nested more than 3 times, see image below.