Complex SQL query

db.sql

WITH ForumMembersWithRanked AS (
    WITH ForumMembersRanked AS (
        SELECT
            "members".*,
            "user"."picture" AS "user.picture",
            ROW_NUMBER() OVER (PARTITION BY "members"."ForumId" ORDER BY "members"."id") AS row_num
        FROM
            "ForumMembers" AS "members"
        LEFT OUTER JOIN
            "Users" AS "user" ON "members"."UserId" = "user"."id"
    )
    SELECT
        "Forum"."id" AS "fid",
        ARRAY_AGG(
            JSONB_BUILD_OBJECT(
                'picture', "members"."user.picture" -- Include only the picture attribute
            )
        ) AS "membersArray"  -- Aggregate three members as an array
    FROM
        (SELECT
            "Forum"."id"
         FROM "Forum" AS "Forum"
         GROUP BY "Forum"."id" LIMIT 1000 OFFSET 0) AS "Forum"
    LEFT OUTER JOIN
        ForumMembersRanked AS "members" ON "Forum"."id" = "members"."ForumId" AND "members"."row_num" <= 3
    GROUP BY
        "Forum"."id"
) SELECT
    "Forum".*,
    "forumCreator"."email" AS "forumCreator.email",
    "forumCreator"."id" AS "forumCreator.id",
    "forumCreator"."name" AS "forumCreator.name",
    "forumCreator"."picture" AS "forumCreator.picture",
    COUNT("members"."id") AS "numMembers",  -- Aggregate the number of members
    "membersList"."membersArray" AS "membersArray"  -- Include the members array
FROM
    (SELECT
        "Forum"."id",
        "Forum"."name",
        "Forum"."description",
        "Forum"."createdAt",
        "Forum"."topics",
        "Forum"."UserId"
     FROM "Forum" AS "Forum"
     GROUP BY "Forum"."id" LIMIT 1000 OFFSET 0) AS "Forum"
LEFT OUTER JOIN
    "Users" AS "forumCreator" ON "Forum"."UserId" = "forumCreator"."id" AND ("forumCreator"."deletedAt" IS NULL)
LEFT OUTER JOIN
    "ForumMembers" AS "members" ON "Forum"."id" = "members"."ForumId"
LEFT OUTER JOIN
    "ForumCategoriesJunction" AS "ForumCategoriesJunction" ON "Forum"."id" = "ForumCategoriesJunction"."forumId"
LEFT OUTER JOIN
    ForumMembersWithRanked AS "membersList" ON "Forum"."id" = "membersList"."fid"
GROUP BY
    "Forum"."id",
    "forumCreator"."email",
    "forumCreator"."id",
    "forumCreator"."name",
    "forumCreator"."picture",
    "Forum"."name",
    "Forum"."description",
    "Forum"."createdAt",
    "Forum"."topics",
    "Forum"."UserId",
    "membersList"."membersArray";