MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE forum_categories
    (`name` varchar(18), `label` varchar(52), `id` int)
;
    
INSERT INTO forum_categories
    (`name`, `label`, `id`)
VALUES
    ('General Discussion', 'Talk about anything and everything Digimon!', 1),
    ('Deck Discussion', 'Talk about Digimon TCG Decks and Strategies!', 2),
    ('Card Discussion', 'Talk about Digimon TCG Cards!', 3),
    ('Website Feedback', 'A place to discuss and offer feedback on the website', 4)
;

CREATE TABLE forum_topics
    (`name` varchar(18), `id` int , `parent_id` int, `author_id` int, date date)
;
    
INSERT INTO forum_topics
    (`name`, `id`, `parent_id`, `author_id`, `date`)
VALUES
    ('My First Topic', 1, 1, 16, '2021-03-29'),
    ('My Second Topic', 2, 1, 16, '2021-03-30')
;

CREATE TABLE forum_topics_content
    (`id` int, `topic_id` int, `author_id` int, date date, `content` varchar(300))
;
    
INSERT INTO forum_topics_content
    (`id`, `topic_id`, `author_id`, `date`, `content`)
VALUES
    (1, 1, 16, '2021-03-29 15:46:55', 'Hey guys! This is my first post!'),
    (2, 1, 16, '2021-03-30 08:05:13', 'This is my first topic reply!')
;
SELECT forum_categories.name, 
  forum_categories.label, 
  forum_categories.id,
  COUNT(DISTINCT(forum_topics.id)) as 'topics',
  COUNT(DISTINCT(forum_topics_content.id)) as 'posts'
   ,SUBSTRING(ANY_VALUE(forum_topics.name),1, 32) as 'thread'
FROM forum_categories 
LEFT JOIN forum_topics ON forum_categories.id = forum_topics.parent_id
LEFT JOIN forum_topics_content ON forum_topics.id = forum_topics_content.topic_id
GROUP BY forum_categories.id,forum_categories.name,forum_categories.label
ORDER BY forum_categories.id 
   , ANY_VALUE(forum_topics.date) DESC


name
label
id
topics
posts
thread


General Discussion
Talk about anything and everything Digimon!
1
2
2
My First Topic

Deck Discussion
Talk about Digimon TCG Decks and Strategies!
2
0
0
null

Card Discussion
Talk about Digimon TCG Cards!
3
0
0
null

Website Feedback
A place to discuss and offer feedback on the website
4
0
0
null


SELECT forum_categories.name, 
  forum_categories.label, 
  forum_categories.id,
  COUNT(DISTINCT(forum_topics.id)) as `topics`,
  COUNT(DISTINCT(forum_topics_content.id)) as `posts`
  ,COUNT(forum_topics.name)
  ,GROUP_CONCAT( SUBSTRING(forum_topics.name,1,32)
                 ORDER BY forum_topics.`date` DESC 
                 SEPARATOR '~') As debug
  ,SUBSTRING_INDEX(GROUP_CONCAT( SUBSTRING(forum_topics.name,1,32)
                 ORDER BY forum_topics.`date` DESC 
                 SEPARATOR '~'),
                 '~',1) AS `thread`
FROM forum_categories 
LEFT JOIN forum_topics ON forum_categories.id = forum_topics.parent_id
LEFT JOIN forum_topics_content ON forum_topics.id = forum_topics_content.topic_id
GROUP BY forum_categories.id,forum_categories.name,forum_categories.label
ORDER BY forum_categories.id;


name
label
id
topics
posts
COUNT(forum_topics.name)
debug
thread


General Discussion
Talk about anything and everything Digimon!
1
2
2
3
My Second Topic~My First Topic~My First Topic
My Second Topic

Deck Discussion
Talk about Digimon TCG Decks and Strategies!
2
0
0
0
null
null

Card Discussion
Talk about Digimon TCG Cards!
3
0
0
0
null
null

Website Feedback
A place to discuss and offer feedback on the website
4
0
0
0
null
null

name	label	id	topics	posts	thread
General Discussion	Talk about anything and everything Digimon!	1	2	2	My First Topic
Deck Discussion	Talk about Digimon TCG Decks and Strategies!	2	0	0	null
Card Discussion	Talk about Digimon TCG Cards!	3	0	0	null
Website Feedback	A place to discuss and offer feedback on the website	4	0	0	null

name	label	id	topics	posts	COUNT(forum_topics.name)	debug	thread
General Discussion	Talk about anything and everything Digimon!	1	2	2	3	My Second Topic~My First Topic~My First Topic	My Second Topic
Deck Discussion	Talk about Digimon TCG Decks and Strategies!	2	0	0	0	null	null
Card Discussion	Talk about Digimon TCG Cards!	3	0	0	0	null	null
Website Feedback	A place to discuss and offer feedback on the website	4	0	0	0	null	null