I’m looking for some help to modify this SQL query to also include the number of solutions for the categories and the number of replies:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT c.id category_id, COUNT(DISTINCT(t.id)) topics, COUNT(p.id) posts, sum(p.like_count) likes, sum(p.reads) reads
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)
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
Currently the output is like this for that query which is very useful but i’d like to include those other metrics.
I’ve tried to tinker with it but my SQL skills are very poor - If anyone could point me in the right direction or help with the query I would be very grateful
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
,
