SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (interview_id INT, interviewee_uid INT, interviewer_uid INT, idate DATE)
INSERT INTO test (interview_id, interviewee_uid, interviewer_uid, idate) VALUES
(1, 1, 11, '2019-07-29'),
(2, 1, 14, '2019-07-28'),
(3, 1, 13, '2019-07-27'),
(4, 1, 14, '2019-07-26'),
(5, 1, 12, '2019-07-25'),
(6, 2, 11, '2019-07-29'),
(7, 2, 13, '2019-07-28'),
(8, 3, 12, '2019-07-29'),
(9, 3, 12, '2019-07-28'),
(10, 3, 12, '2019-07-27'),
(11, 3, 13, '2019-07-26'),
(12, 4, 11, '2019-07-29'),
(13, 4, 11, '2019-07-28')
13 rows affected

WITH cte AS (
SELECT *,
       DENSE_RANK() OVER (PARTITION BY interviewee_uid ORDER BY interviewer_uid) iid,
       ROW_NUMBER() OVER (PARTITION BY interviewee_uid, interviewer_uid ORDER BY idate DESC) did
FROM test
)
SELECT * 
FROM cte
WHERE did = 1
  AND iid <= 3 
ORDER BY 1;


interview_id
interviewee_uid
interviewer_uid
idate
iid
did


1
1
11
29/07/2019 00:00:00
1
1

3
1
13
27/07/2019 00:00:00
3
1

5
1
12
25/07/2019 00:00:00
2
1

6
2
11
29/07/2019 00:00:00
1
1

7
2
13
28/07/2019 00:00:00
2
1

8
3
12
29/07/2019 00:00:00
1
1

11
3
13
26/07/2019 00:00:00
2
1

12
4
11
29/07/2019 00:00:00
1
1


WITH 
cte1 AS ( SELECT interview_id, interviewee_uid, interviewer_uid, idate,
                 ROW_NUMBER() OVER (PARTITION BY interviewee_uid, 
                                                 interviewer_uid 
                                    ORDER BY idate DESC) did
          FROM test ),
cte2 AS ( SELECT interview_id, interviewee_uid, interviewer_uid, idate,
                 ROW_NUMBER() OVER (PARTITION BY interviewee_uid 
                                    ORDER BY idate DESC) did
          FROM cte1
          WHERE did = 1
)
SELECT interview_id, interviewee_uid, interviewer_uid, idate 
FROM cte2 
WHERE did <= 3
ORDER BY 1
;


interview_id
interviewee_uid
interviewer_uid
idate


1
1
11
29/07/2019 00:00:00

2
1
14
28/07/2019 00:00:00

3
1
13
27/07/2019 00:00:00

6
2
11
29/07/2019 00:00:00

7
2
13
28/07/2019 00:00:00

8
3
12
29/07/2019 00:00:00

11
3
13
26/07/2019 00:00:00

12
4
11
29/07/2019 00:00:00

interview_id	interviewee_uid	interviewer_uid	idate	iid	did
1	1	11	29/07/2019 00:00:00	1	1
3	1	13	27/07/2019 00:00:00	3	1
5	1	12	25/07/2019 00:00:00	2	1
6	2	11	29/07/2019 00:00:00	1	1
7	2	13	28/07/2019 00:00:00	2	1
8	3	12	29/07/2019 00:00:00	1	1
11	3	13	26/07/2019 00:00:00	2	1
12	4	11	29/07/2019 00:00:00	1	1