SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE UserProfile ( 
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( 
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) 
);

CREATE TABLE Friendship ( 
    RequesterId     INT      NOT NULL,
    AddresseeId     INT      NOT NULL, 
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId), 
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendsAreDistinct_CK    CHECK       (RequesterId <> AddresseeId)
);

--
CREATE TABLE MyStatus ( 
    StatusCode CHAR(1)  NOT NULL,
    Name       CHAR(30) NOT NULL,
    --
    CONSTRAINT MyStatus_PK PRIMARY KEY (StatusCode),
    CONSTRAINT MyStatus_AK UNIQUE      (Name) 
); 

CREATE TABLE FriendshipStatus ( 
    RequesterId       INT      NOT NULL,
    AddresseeId       INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StatusCode        CHAR(1)  NOT NULL,
    SpecifierId       INT      NOT NULL,
    --
    CONSTRAINT FriendshipStatus_PK             PRIMARY KEY (RequesterId, AddresseeId, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipStatusToFriendship_FK FOREIGN KEY (RequesterId, AddresseeId)
        REFERENCES Friendship  (RequesterId, AddresseeId), 
    CONSTRAINT FriendshipStatusToMyStatus_FK   FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipStatusToSpecifier_FK  FOREIGN KEY (SpecifierId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendsAreDifferent_CK          CHECK       (RequesterId <> AddresseeId)        
);
-- Populating all the tables involved.
INSERT INTO UserProfile 
    (UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
    (1748, 'Frank', 'Codd', '1923-08-19','M', 'ted.codd', '2016-03-10 05:12:04.000'),
    (1750, 'Alan', 'Turing', '1912-06-23','M', 'a.turing','2016-03-10 05:12:04.000'),
    (1825, 'Konrad', 'Zuse', '1910-06-22','M', 'k.zuse', '2016-03-10 05:12:04.000'),
    (3691, 'Grace', 'Hopper', '1906-12-09','F', 'g.hopper', '2016-03-10 05:12:04.000'),
    (4247, 'Alan', 'Kay', '1940-05-17','M', 'a.kay', '2016-03-10 05:12:04.000'),
    (5342, 'Margaret', 'Hamilton', '1963-08-17','F', 'm.hamilton', '2016-03-10 05:12:04.000');

INSERT INTO Friendship 
    (RequesterId, AddresseeId, CreatedDateTime)
VALUES
    (1750, 1748, '2016-04-01 16:58:12.000'),
    (1825, 4247, '2016-03-10 18:03:04.000'),
    (1748, 5342, '2016-03-10 17:14:04.000'),
    (3691, 1750, '2016-03-10 03:55:04.000');
    
INSERT INTO MyStatus 
    (StatusCode, Name)
VALUES
    ('R', 'Requested'),
    ('A', 'Accepted'),
    ('D', 'Declined'),
    ('B', 'Blocked');
    
INSERT INTO FriendshipStatus 
    (RequesterId, AddresseeId, SpecifiedDateTime, StatusCode, SpecifierId)
VALUES
    (1750, 1748, '2016-04-01 16:58:12.000', 'R', 1750),
    (1750, 1748, '2016-04-02 09:13:08.000', 'A', 1748),
    (1750, 1748, '2016-04-02 11:02:06.000', 'B', 1748),
    (1825, 4247, '2016-05-11 16:28:03.000', 'R', 1825),
    (1825, 4247, '2016-05-11 19:18:09.000', 'A', 4247),
    (1748, 5342, '2016-06-11 11:13:05.000', 'R', 1748),
    (1748, 5342, '2016-06-12 19:03:07.000', 'D', 5342),
    (3691, 1750, '2016-06-20 16:28:05.000', 'R', 3691),
    (3691, 1750, '2016-06-20 16:51:10.000', 'A', 1750),
    (3691, 1750, '2016-06-21 08:05:05.000', 'B', 1750);
24 rows affected

-- Example of a row rejected by the constraint FriendsAreDistinct_CK in the Friendship table.
INSERT INTO Friendship 
    (RequesterId, AddresseeId, CreatedDateTime)
VALUES
    (1750, 1750, '2022-03-31 16:58:12.000');
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the CHECK constraint "FriendsAreDistinct_CK". The conflict occurred in database "fiddle_41ddae82c1e7462da8d8523dd326d520", table "dbo.Friendship".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

-- Getting the full row retaining the Status info of the 
-- Friendship between UserId 1750 and UserId 1748.
SELECT FS.RequesterId,
       FS.AddresseeId,
       FS.SpecifiedDateTime,       
       FS.StatusCode AS CurrentStatusCode,
       FS.SpecifierId
  FROM FriendshipStatus FS
 WHERE FS.RequesterId = 1750
   AND FS.AddresseeId = 1748
   AND FS.SpecifiedDateTime = (
                                   SELECT MAX(NestedFS.SpecifiedDateTime)
                                         FROM FriendshipStatus NestedFS
                                        WHERE NestedFS.RequesterId = FS.RequesterId
                                          AND NestedFS.AddresseeId = FS.AddresseeId
                              );


RequesterId
AddresseeId
SpecifiedDateTime
CurrentStatusCode
SpecifierId


1750
1748
2016-04-02 11:02:06.000
B
1748


-- Getting only the current or most-recent StatusCode value of the
-- Friendship between UserId 1750 and UserId 1748.
SELECT FS.StatusCode AS CurrentStatusCode
  FROM FriendshipStatus FS
 WHERE FS.RequesterId = 1750
   AND FS.AddresseeId = 1748
   AND FS.SpecifiedDateTime = (
                                   SELECT MAX(NestedFS.SpecifiedDateTime)
                                         FROM FriendshipStatus NestedFS
                                        WHERE NestedFS.RequesterId = FS.RequesterId
                                          AND NestedFS.AddresseeId = FS.AddresseeId
                              );


CurrentStatusCode


B


-- The following queries are useful in order to test the results of the two queries shown above.
SELECT StatusCode,
       Name
  FROM MyStatus;


StatusCode
Name


A
Accepted

B
Blocked

D
Declined

R
Requested


-- Getting all UserProfile rows with Id and FullName.
SELECT UserId,
       CONCAT(FirstName, LastName) AS FullName
  FROM UserProfile;


UserId
FullName


4247
Alan                          Kay

1750
Alan                          Turing

1748
Frank                         Codd

3691
Grace                         Hopper

1825
Konrad                        Zuse

5342
Margaret                      Hamilton


-- Getting all FriendshipStatus ordered rows.
SELECT FS.RequesterId,
       FS.AddresseeId,
       FS.SpecifiedDateTime,       
       FS.StatusCode AS CurrentStatusCode,
       FS.SpecifierId
    FROM FriendshipStatus FS
ORDER BY SpecifiedDateTime ASC;


RequesterId
AddresseeId
SpecifiedDateTime
CurrentStatusCode
SpecifierId


1750
1748
2016-04-01 16:58:12.000
R
1750

1750
1748
2016-04-02 09:13:08.000
A
1748

1750
1748
2016-04-02 11:02:06.000
B
1748

1825
4247
2016-05-11 16:28:03.000
R
1825

1825
4247
2016-05-11 19:18:09.000
A
4247

1748
5342
2016-06-11 11:13:05.000
R
1748

1748
5342
2016-06-12 19:03:07.000
D
5342

3691
1750
2016-06-20 16:28:05.000
R
3691

3691
1750
2016-06-20 16:51:10.000
A
1750

3691
1750
2016-06-21 08:05:05.000
B
1750


-- Getting all FriendshipStatus info along with Requester and Addressee FullNames.
SELECT FS.RequesterId,
       CONCAT(R.FirstName,' ', R.LastName) AS RequesterFullName,
       FS.AddresseeId,
       CONCAT(A.FirstName, ' ', A.LastName) AS AddresseeFullName,
       FS.StatusCode AS CurrentStatusCode,
       FS.SpecifiedDateTime,
       FS.SpecifierId
    FROM FriendshipStatus FS
    JOIN UserProfile R
      ON R.UserId = FS.RequesterId
    JOIN UserProfile A
      ON A.UserId = FS.AddresseeId;


RequesterId
RequesterFullName
AddresseeId
AddresseeFullName
CurrentStatusCode
SpecifiedDateTime
SpecifierId


1748
Frank                          Codd
5342
Margaret                       Hamilton
R
2016-06-11 11:13:05.000
1748

1748
Frank                          Codd
5342
Margaret                       Hamilton
D
2016-06-12 19:03:07.000
5342

1750
Alan                           Turing
1748
Frank                          Codd
R
2016-04-01 16:58:12.000
1750

1750
Alan                           Turing
1748
Frank                          Codd
A
2016-04-02 09:13:08.000
1748

1750
Alan                           Turing
1748
Frank                          Codd
B
2016-04-02 11:02:06.000
1748

1825
Konrad                         Zuse
4247
Alan                           Kay
R
2016-05-11 16:28:03.000
1825

1825
Konrad                         Zuse
4247
Alan                           Kay
A
2016-05-11 19:18:09.000
4247

3691
Grace                          Hopper
1750
Alan                           Turing
R
2016-06-20 16:28:05.000
3691

3691
Grace                          Hopper
1750
Alan                           Turing
A
2016-06-20 16:51:10.000
1750

3691
Grace                          Hopper
1750
Alan                           Turing
B
2016-06-21 08:05:05.000
1750


-- Getting all FriendshipStatus ordered rows for the Friendship 
-- between UserId 1750 and UserId 1748.
SELECT FS.RequesterId,
       FS.AddresseeId,
       FS.SpecifiedDateTime,       
       FS.StatusCode AS CurrentStatusCode,
       FS.SpecifierId
    FROM FriendshipStatus FS
   WHERE FS.RequesterId = 1750
     AND FS.AddresseeId = 1748
ORDER BY SpecifiedDateTime DESC;


RequesterId
AddresseeId
SpecifiedDateTime
CurrentStatusCode
SpecifierId


1750
1748
2016-04-02 11:02:06.000
B
1748

1750
1748
2016-04-02 09:13:08.000
A
1748

1750
1748
2016-04-01 16:58:12.000
R
1750
UserId	FullName
4247	Alan Kay
1750	Alan Turing
1748	Frank Codd
3691	Grace Hopper
1825	Konrad Zuse
5342	Margaret Hamilton
RequesterId	AddresseeId	SpecifiedDateTime	CurrentStatusCode	SpecifierId
1750	1748	2016-04-01 16:58:12.000	R	1750
1750	1748	2016-04-02 09:13:08.000	A	1748
1750	1748	2016-04-02 11:02:06.000	B	1748
1825	4247	2016-05-11 16:28:03.000	R	1825
1825	4247	2016-05-11 19:18:09.000	A	4247
1748	5342	2016-06-11 11:13:05.000	R	1748
1748	5342	2016-06-12 19:03:07.000	D	5342
3691	1750	2016-06-20 16:28:05.000	R	3691
3691	1750	2016-06-20 16:51:10.000	A	1750
3691	1750	2016-06-21 08:05:05.000	B	1750