SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Contacts(uniqueId INTEGER PRIMARY KEY, username TEXT);
INSERT INTO Contacts (username) VALUES
  ('me'), ('John Doe'), ('Alice Shrek');
SELECT * FROM Contacts;


uniqueId
username


1
me

2
John Doe

3
Alice Shrek


CREATE TABLE Messages(isRead Bool, sender Blob, receiver Blob, timestamp Integer);
INSERT INTO Messages (isRead, sender, receiver, timestamp) VALUES 
  (true, 1, 2, 1), (false, 2, 1, 2),
  (true, 1, 3, 3), (false, 3, 1, 4);
WITH
  person(uniqueId) AS (VALUES (1)),
  aggregates AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY MIN(m.sender, m.receiver), MAX(m.sender, m.receiver) ORDER BY timestamp DESC) rn,
           TOTAL(NOT m.isRead) OVER (PARTITION BY MIN(m.sender, m.receiver), MAX(m.sender, m.receiver)) unreadCount
    FROM Messages m INNER JOIN person p 
    ON p.uniqueId IN (m.sender, m.receiver)
  )
SELECT c.username, c.uniqueId,
       a.sender, a.receiver, a.timestamp, a.unreadCount 
FROM aggregates a CROSS JOIN person p 
INNER JOIN Contacts c 
ON c.uniqueId = CASE WHEN a.sender = p.uniqueId THEN a.receiver ELSE a.sender END
WHERE a.rn = 1;


username
uniqueId
sender
receiver
timestamp
unreadCount


John Doe
2
2
1
2
1

Alice Shrek
3
3
1
4
1