By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Table_One (
ID int primary key,
Name text
);
insert into Table_One values
(1,'John'),
(2,'Tom'),
(3,'Anna');
create table Table_Two (
ID int,
VisitId int,
date date,
read_receipt tinyint,
primary key (ID, VisitId)
);
insert into Table_Two values
(1,2513,'2001-05-05',1),
(1,84654,'2012-10-05',1),
(1,454,'2018-04-20',1),
(2,754,'1999-04-05',0),
(2,654,'2010-08-08',1),
(2,624,'1982-04-09',1),
(3,7546,'1997-07-03',0),
(3,246574,'2015-06-04',1),
(3,15487,'2017-03-04',1);
Records: 3 Duplicates: 0 Warnings: 0
Records: 9 Duplicates: 0 Warnings: 0
SELECT t1.ID, t1.Name, t2a.VisitID, t2a.Date, t2b.sum_read_receipt
FROM Table_One t1
INNER JOIN (SELECT ID, SUM(read_receipt) AS sum_read_receipt FROM Table_Two GROUP BY ID) t2b ON t2b.ID = t1.ID
JOIN LATERAL (SELECT VisitID, Date FROM Table_Two t2a WHERE t2a.ID = t1.ID ORDER BY Date DESC LIMIT 1) t2a ON 1=1
| ID | Name | VisitID | Date | sum_read_receipt |
|---|---|---|---|---|
| 1 | John | 454 | 2018-04-20 | 3 |
| 2 | Tom | 654 | 2010-08-08 | 2 |
| 3 | Anna | 15487 | 2017-03-04 | 2 |