I was able to get something working, with some AI help, that suits my own context (You may need to adjust the solved reference). 
Sharing the final solutions incase others might be able to find a use for this too. 
The first iteration:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT
c.id AS category_id,
COUNT(DISTINCT t.id) AS topics,
COUNT(p.id) AS posts,
SUM(p.like_count) AS likes,
SUM(p.reads) AS reads,
COUNT(DISTINCT CASE WHEN tf.name = 'accepted_solution_id' THEN t.id END) AS solutions,
COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN topic_custom_fields tf ON t.id = tf.topic_id AND tf.name = 'accepted_solution_id'
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
The final solution to my specific use case:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT
c.id AS category_id,
COUNT(DISTINCT t.id) AS topics,
COUNT(p.id) AS posts,
SUM(p.like_count) AS likes,
SUM(p.reads) AS reads,
COUNT(DISTINCT CASE WHEN ua.action_type = 15 THEN t.id END) AS solutions,
COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.action_type = 15
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
