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 ID, Name, VisitID, Date, sum_read_receipt
FROM (
SELECT t1.ID, t1.Name, t2.VisitID, t2.Date,
row_number() over (PARTITION BY t1.ID ORDER BY t2.Date DESC) rn,
count(case when read_receipt = 1 then 1 end) over (PARTITION BY t2.ID) AS sum_read_receipt
FROM Table_One t1
INNER JOIN Table_Two t2 ON t2.ID = t1.ID
) t
WHERE rn = 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 |