返信と解決済みメトリクスを含めるようにレポートを変更する

[Danny_Dainton]

チームの皆さん、こんにちは

このSQLクエリを修正して、カテゴリごとのソリューション数と返信数も表示できるように、いくつか助けを求めています。

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

現在、このクエリの出力は非常に役立ちますが、これらの他の指標も追加したいと思います。

自分でいじってみましたが、SQLのスキルが非常に乏しいです。どなたか正しい方向を指し示していただくか、クエリのヘルプをいただけると大変ありがたいです

[Danny_Dainton]

AIの助けを借りて、自分のコンテキストに合ったものが機能するようになりました（solved 参照を調整する必要があるかもしれません）。

他の人もこのソリューションを利用できるかもしれないので、最終的なソリューションを共有します。

最初のイテレーション:

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

私の特定のユースケースの最終ソリューション:

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