By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @t TABLE (SubjectID INT, Date Date, Score INT);
INSERT INTO @t VALUES
(1, '2018-01-01', 150),
(1, '2018-01-07', 151), -- 6 days gap
(2, '2018-01-01', 152),
(2, '2018-01-08', 159), -- 7 days gap
(3, '2018-01-01', 155),
(3, '2018-01-08', 157), -- 7 days gap
(3, '2018-01-09', 157); -- 1 days gap, ignored although there is 8 days gap two rows ago
SELECT *
FROM @t AS curr
WHERE Score > 150
AND Date >= (
SELECT DATEADD(DAY, 7, MAX(Date)) -- maximum date + 7 days
FROM @t AS prev
WHERE SubjectID = curr.SubjectID
AND Date < curr.Date -- prev dates
AND Score > 150
)
| SubjectID | Date | Score |
|---|---|---|
| 2 | 08/01/2018 00:00:00 | 159 |
| 3 | 08/01/2018 00:00:00 | 157 |