EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH Lookup AS (
    SELECT
        studies.publicId,
        studies.internalId,
        ROW_NUMBER() OVER (ORDER BY studies.publicId) AS rowNumber
    FROM Resources AS studies
    INNER JOIN DicomIdentifiers t0
        ON t0.id = studies.internalId
       AND t0.tagGroup = 8
       AND t0.tagElement = 32
    INNER JOIN DicomIdentifiers t1
        ON t1.id = studies.internalId
       AND t1.tagGroup = 8
       AND t1.tagElement = 32
    INNER JOIN DicomIdentifiers t2
        ON t2.id = studies.internalId
       AND t2.tagGroup = 16
       AND t2.tagElement = 32
    WHERE studies.resourceType = 1
      AND t0.value >= '20240101'
      AND t1.value <= '20241231'
      AND t2.value = '202400846'
),
ValidGrandChildCounts AS (
    SELECT
        thisLevel.internalId,
        COALESCE(SUM(childLevel.childCount), 0) AS totalGrandChildCount
    FROM Resources AS thisLevel
    LEFT JOIN Resources AS childLevel
        ON childLevel.parentId = thisLevel.internalId
    WHERE NOT EXISTS (
        SELECT 1
        FROM InvalidChildCounts
        WHERE id = thisLevel.internalId
    )
      AND NOT EXISTS (
        SELECT 1
        FROM Resources AS child
        JOIN InvalidChildCounts
          ON InvalidChildCounts.id = child.internalId
        WHERE child.parentId = thisLevel.internalId
    )
    GROUP BY thisLevel.internalId
)
SELECT
    1 AS c0_queryId,
    Lookup.internalId AS c1_internalId,
    Lookup.rowNumber AS c2_rowNumber,
    Lookup.publicId AS c3_string1,
    NULL::TEXT AS c4_string2,
    NULL::TEXT AS c5_string3,
    NULL::BYTEA AS c6_string4,
    NULL::INT AS c7_int1,
    NULL::INT AS c8_int2,
    NULL::INT AS c9_int3,
    NULL::BIGINT AS c10_big_int1,
    NULL::BIGINT AS c11_big_int2
FROM Lookup

UNION ALL

SELECT
    2 AS c0_queryId,
    Lookup.internalId AS c1_internalId,
    NULL::BIGINT AS c2_rowNumber,
    value AS c3_string1,
    NULL::TEXT AS c4_string2,
    NULL::TEXT AS c5_string3,
    NULL::BYTEA AS c6_string4,
    tagGroup AS c7_int1,
    tagElement AS c8_int2,
    NULL::INT AS c9_int3,
    NULL::BIGINT AS c10_big_int1,
    NULL::BIGINT AS c11_big_int2
FROM Lookup
INNER JOIN MainDicomTags
    ON MainDicomTags.id = Lookup.internalId

UNION ALL

SELECT
    4 AS c0_queryId,
    Lookup.internalId AS c1_internalId,
    NULL::BIGINT AS c2_rowNumber,
    value AS c3_string1,
    NULL::TEXT AS c4_string2,
    NULL::TEXT AS c5_string3,
    NULL::BYTEA AS c6_string4,
    type AS c7_int1,
    revision AS c8_int2,
    NULL::INT AS c9_int3,
    NULL::BIGINT AS c10_big_int1,
    NULL::BIGINT AS c11_big_int2
FROM Lookup
INNER JOIN Metadata
    ON Metadata.id = Lookup.internalId

UNION ALL

SELECT
    21 AS c0_queryId,
    Lookup.internalId AS c1_internalId,
    NULL::BIGINT AS c2_rowNumber,
    value AS c3_string1,
    NULL::TEXT AS c4_string2,
    NULL::TEXT AS c5_string3,
    NULL::BYTEA AS c6_string4,
    tagGroup AS c7_int1,
    tagElement AS c8_int2,
    NULL::INT AS c9_int3,
    NULL::BIGINT AS c10_big_int1,
    NULL::BIGINT AS c11_big_int2
FROM Lookup
INNER JOIN Resources childLevel
    ON childLevel.parentId = Lookup.internalId
INNER JOIN MainDicomTags
    ON MainDicomTags.id = childLevel.internalId
   AND (tagGroup, tagElement) IN ((8, 96))

UNION ALL

SELECT
    33 AS c0_queryId,
    Lookup.internalId AS c1_internalId,
    NULL::BIGINT AS c2_rowNumber,
    NULL::TEXT AS c3_string1,
    NULL::TEXT AS c4_string2,
    NULL::TEXT AS c5_string3,
    NULL::BYTEA AS c6_string4,
    NULL::INT AS c7_int1,
    NULL::INT AS c8_int2,
    NULL::INT AS c9_int3,
    COALESCE(
        (
            SELECT totalGrandChildCount
            FROM ValidGrandChildCounts
            WHERE internalId = Lookup.internalId
        ),
        (
            SELECT COUNT(grandChildLevel.internalId)
            FROM Resources AS childLevel
            INNER JOIN Resources AS grandChildLevel
                ON childLevel.internalId = grandChildLevel.parentId
            WHERE Lookup.internalId = childLevel.parentId
        )
    ) AS c10_big_int1,
    NULL::BIGINT AS c11_big_int2
FROM Lookup

ORDER BY c0_queryId, c2_rowNumber;