Modificar un reporte para incluir las métricas de Replies y Solved

[Danny_Dainton]

Hola equipo ,

Estoy buscando ayuda para modificar esta consulta SQL para que también incluya el número de soluciones para las categorías y el número de respuestas:

-- [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

Actualmente, la salida es como esta para esa consulta, lo cual es muy útil, pero me gustaría incluir esas otras métricas.

He intentado modificarla, pero mis habilidades de SQL son muy pobres. Si alguien pudiera orientarme o ayudarme con la consulta, estaría muy agradecido

[Danny_Dainton]

Pude hacer que algo funcionara, con algo de ayuda de IA, que se adapta a mi propio contexto (es posible que necesites ajustar la referencia a solved).

Comparto las soluciones finales por si a otros les resulta útil.

La primera iteración:

-- [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

La solución final para mi caso de uso específico:

-- [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