SELECT 
    d.""RequestId"",
    d.""ProjectFileId"",
    COUNT(d.""Id"") AS ""NumberOfDocuments"",
    MAX(d.""DateOfRequest"") AS ""DateOfLatestRequest"",
    
    -- Count of APPROVED documents
    (
        SELECT COUNT(a.""Id"")
        FROM dbo.""DocumentExecutiveApprovalRequests"" sub_d
        INNER JOIN dbo.""DigitalFileSignedApprovals"" a 
            ON sub_d.""Id"" = a.""ApprovalRequestId""
        WHERE sub_d.""RequestId"" = d.""RequestId"" 
          AND sub_d.""ProjectFileId"" = d.""ProjectFileId""
    ) AS ""NumberOfApprovedDocuments"",
    
    -- Count of UNAPPROVED documents (This value will be used for filtering)
    (
        SELECT COUNT(sub_d.""Id"")
        FROM dbo.""DocumentExecutiveApprovalRequests"" sub_d
        LEFT JOIN dbo.""DigitalFileSignedApprovals"" a 
            ON sub_d.""Id"" = a.""ApprovalRequestId""
        WHERE sub_d.""RequestId"" = d.""RequestId"" 
          AND sub_d.""ProjectFileId"" = d.""ProjectFileId""
          AND a.""Id"" IS NULL 
    ) AS ""NumberOfUnapprovedDocuments"",
    
    pf.""Title"" AS ""ProjectTitle"",
    pp.""Name"" AS ""ProjectPriority"", 
    st.""Name"" AS ""ProjectStatus"",   
    pf.""Description"" AS ""ProjectDescription"",
    
    pf.""OwnerUserId"" AS ""ProjectOwnerId"",
    CONCAT(u_owner.""FirstName"", ' ', u_owner.""LastName"") AS ""ProjectOwnerName"",

    -- Subqueries to get the Requester ID and Name of the user who made the LATEST request
    (
        SELECT sub.""RequestedByUserId"" 
        FROM dbo.""DocumentExecutiveApprovalRequests"" sub
        WHERE sub.""RequestId"" = d.""RequestId"" AND sub.""ProjectFileId"" = d.""ProjectFileId""
        ORDER BY sub.""DateOfRequest"" DESC LIMIT 1
    ) AS ""RequestedByUserId"",
    (
        SELECT CONCAT(u_req.""FirstName"", ' ', u_req.""LastName"")
        FROM dbo.""Users"" u_req
        WHERE u_req.""Id"" = (
            SELECT sub.""RequestedByUserId"" 
            FROM dbo.""DocumentExecutiveApprovalRequests"" sub
            WHERE sub.""RequestId"" = d.""RequestId"" AND sub.""ProjectFileId"" = d.""ProjectFileId""
            ORDER BY sub.""DateOfRequest"" DESC LIMIT 1
        )
    ) AS ""RequestedByName""

FROM 
    dbo.""DocumentExecutiveApprovalRequests"" d

-- 1. Join to get Project details
INNER JOIN 
    dbo.""ProjectFiles"" pf ON d.""ProjectFileId"" = pf.""Id""

-- 2. Join to get Project Owner details
LEFT JOIN 
    dbo.""Users"" u_owner ON pf.""OwnerUserId"" = u_owner.""Id""

-- 3. Join to get Project Priority Name
INNER JOIN 
    dbo.""ProjectPriorities"" pp ON pf.""PriorityId"" = pp.""Value""

-- 4. Join to get Project Status Name
INNER JOIN 
    dbo.""StatusOfTasks"" st ON pf.""StatusId"" = st.""Value""
    
GROUP BY 
    d.""RequestId"", 
    d.""ProjectFileId"", 
    pf.""Title"", 
    pf.""Description"", 
    pf.""OwnerUserId"",
    pp.""Name"", st.""Name"",
    u_owner.""FirstName"", u_owner.""LastName""

-- Use HAVING to filter the grouped result based on the count.
HAVING 
    (
        SELECT COUNT(sub_d.""Id"")
        FROM dbo.""DocumentExecutiveApprovalRequests"" sub_d
        LEFT JOIN dbo.""DigitalFileSignedApprovals"" a 
            ON sub_d.""Id"" = a.""ApprovalRequestId""
        WHERE sub_d.""RequestId"" = d.""RequestId"" 
          AND sub_d.""ProjectFileId"" = d.""ProjectFileId""
          AND a.""Id"" IS NULL 
    ) > 0

ORDER BY 
    ""DateOfLatestRequest"" DESC
LIMIT 6;