Count child members from parent value

I have a table like conversations and conversation_timelines

conversations table example

|  id   |   last_active   |   category_id   |
|-------------------------------------------|
|  1    |   1552462134    |        1        |
|  2    |   1552461332    |        1        |
|  3    |   1552462312    |        2        |
|  4    |   1552461772    |        1        |

conversation_timelines table example

|  id   |  conversation_id  |  message  |  created_time  |
|--------------------------------------------------------|
|  1    |        1          |  hi       |  1552462066    |
|  2    |        1          |  hello    |  1552462172    |
|  3    |        1          |  world    |  1552462188    |
|  4    |        2          |  another  |  1552462141    |

What I want to query is Counting the number of records in conversation_timelines that has created_time > conversation's last_active WHERE conversations.category_id = 1

and the result world be like

|  conversation_id  |  count  |
|         1         |    2    |
|         2         |    1    |

Answer

You can use the following solution, using a JOIN with GROUP BY:

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

demo on dbfiddle.uk