Check and count if a column value is used in another column of same table
SELECT t1.id, 
       MAX(CASE WHEN t1.`check` THEN t1.modified_date END) modified_date,
       COUNT(DISTINCT t2.id) children_count,
       1 `check`
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING modified_date IS NOT NULL;


fidddle