As noted in @TravisB's answer, 'naked' SQL isn't an option. However, many SQL functions have been replicated in Esri's own Arcade code system. If you're working with the latest versions of their software (minimum version 10.8 for Enterprise), the Arcade function GroupBy has been included.
See the documentation here. The example Esri gives is as follows:
Here is an example with a linestring layer (Hungarian rivers) having two attributes, "len" - the length of the feature and "NEV" - the name of the feature. Features are between two junctions, so the same name may be used several times. Using the Field calculator I created a virtual column with maximal length of features with the same name:...
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 ...
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
Based on answers I have got I wrote following SQL which seems to catch all the issues I have with lines not meeting st_issimple check:
with all_lines(gid, geom) as (values
( 1, 'LINESTRING (0 0, 1 1, 0 0, 1 1)'::geometry ),
( 2, 'LINESTRING (2 2, 3 3, 3 2, 2 3)'::geometry ),
( 3, ...