By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user_date
(id int, user_id int, activity varchar(3), date varchar(19))
;
INSERT INTO user_date
(id, user_id, activity, date)
VALUES
(1, 1, 'foo', '2020-08-07 00:00:00'),
(2, 1, 'bar', '2020-08-08 00:00:00'),
(3, 1, 'baz', '2020-08-09 00:00:00'),
(4, 2, 'foo', '2020-08-07 00:00:00'),
(5, 2, 'bar', '2020-08-08 00:00:00'),
(6, 2, 'baz', '2020-08-09 00:00:00')
,
(7, 2, 'baz', '2020-08-10 00:00:00'),
(8, 2, 'baz', '2020-08-10 00:00:00'),
(9, 2, 'baz', '2020-08-10 00:00:00'),
(10, 2, 'baz', '2020-08-10 00:00:00'),
(11, 2, 'baz', '2020-08-10 00:00:00'),
(12, 2, 'baz', '2020-08-10 00:00:00'),
(13, 2, 'baz', '2020-08-10 00:00:00'),
(14, 2, 'baz', '2020-08-10 00:00:00'),
(15, 2, 'baz', '2020-08-10 00:00:00'),
(16, 2, 'baz', '2020-08-10 00:00:00')
;
16 rows affected
DELETE u1
FROM
user_date AS u1
JOIN
(
SELECT id
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY o.user_id ORDER BY o.[date] DESC)
AS RowNumber
,o.id
,o.user_id
FROM user_date o
) subquery
WHERE RowNumber > 10 ) u2 ON u1.id = u2.id
3 rows affected
SELECT * FROM user_date
| id | user_id | activity | date |
|---|---|---|---|
| 1 | 1 | foo | 2020-08-07 00:00:00 |
| 2 | 1 | bar | 2020-08-08 00:00:00 |
| 3 | 1 | baz | 2020-08-09 00:00:00 |
| 7 | 2 | baz | 2020-08-10 00:00:00 |
| 8 | 2 | baz | 2020-08-10 00:00:00 |
| 9 | 2 | baz | 2020-08-10 00:00:00 |
| 10 | 2 | baz | 2020-08-10 00:00:00 |
| 11 | 2 | baz | 2020-08-10 00:00:00 |
| 12 | 2 | baz | 2020-08-10 00:00:00 |
| 13 | 2 | baz | 2020-08-10 00:00:00 |
| 14 | 2 | baz | 2020-08-10 00:00:00 |
| 15 | 2 | baz | 2020-08-10 00:00:00 |
| 16 | 2 | baz | 2020-08-10 00:00:00 |