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')
;
DELETE u1
FROM
user_date AS u1
JOIN
(
SELECT id
FROM
(
SELECT @row_num := IF(@prev_value=o.user_id,@row_num+1,1) AS 'RowNumber'
,o.id
,@prev_value := o.user_id
FROM user_date o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY o.user_id, o.`date` DESC
) subquery
WHERE `RowNumber` > 10 ) u2 ON u1.id = u2.id
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 |