Self join to compare rows of table?

We are using SQL Server 2017 for our document management application. In our application one document can have many versions. Access can be given to users at revision level. We store user access information in DOCUMENT_ACL table.

• UserId 1001 , 1002 & 1003 has access to RevisionId 101.
• UserId 1003 & 1004 has access to RevisionId 102.
• UserId 1004 & 1005 has access to RevisionId 103.

We have complex requirement to show only latest revision to user have access to.

We want to write a stored procedure to return resultset as below.

e.g.

For RevisionId 103 (Version 3 - Latest) it should return both UserId 1004 & 1005.

For RevisionId 102 (Version 2) it should return only UserId 1003 (as 1004 user is already return for version 103).

For RevisionId 101 (Version 1) it should return only UserId 1001 & 1002 (as 1003 user is already return for version 102).

We looked at self join to achieve above but it look too complex. Please suggest how we can implement above logic in a stored procedure.

Sounds like a simple group by?

with data as (
    select docid, rev
    , users
    from (
        values  (11, 101, 1001)
        ,   (11, 101, 1002)
        ,   (11, 101, 1003)
        ,   (11, 102, 1003)
        ,   (11, 102, 1004)
        ,   (11, 103, 1004)
        ,   (11, 103, 1005)
        ) x(docid,rev, users)
)
select docid, maxrev, string_agg(users,',') as users
from (
    select  docid, max(rev) as maxrev, users
    from    data
    group by docid, users
    ) x
group by docid, maxrev

Outputs: