By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE conversation (
id INT,
last_active INT,
category_id INT
);
INSERT INTO conversation VALUES
(1, 1552462134, 1),
(2, 1552461332, 1),
(3, 1552462312, 2),
(4, 1552461772, 1);
CREATE TABLE conversation_timelines (
id INT,
conversation_id INT,
message VARCHAR(20),
created_time INT
);
INSERT INTO conversation_timelines VALUES
(1, 1, 'hi', 1552462066),
(2, 1, 'hello', 1552462172),
(3, 1, 'world', 1552462188),
(4, 2, 'another', 1552462141);
SELECT c.id AS `conversation_id`, COUNT(ct.id) AS `count`
FROM conversation c INNER JOIN conversation_timelines ct ON c.id = ct.conversation_id
WHERE ct.created_time > c.last_active AND c.category_id = 1
GROUP BY c.id
| conversation_id | count |
|---|---|
| 1 | 2 |
| 2 | 1 |