# تعديل تقرير لتضمين مقاييس الردود والمحلولة

**URL:** <https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [19 يناير 2024، 10:19ص UTC](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195 "2024-01-19T10:19:18Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![Danny\_Dainton](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/danny_dainton/32/486326_2.png) [@Danny\_Dainton](https://meta.discourse.org/u/Danny_Dainton)\
**Post date:** [19 يناير 2024، 5:27م UTC](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195/3 "2024-01-19T17:27:20Z")

</div>

تمكنت من الحصول على شيء يعمل، بمساعدة الذكاء الاصطناعي، يناسب سياقي الخاص (قد تحتاج إلى تعديل مرجع `solved`). 🏆

أشارك الحلول النهائية في حال تمكن الآخرين من إيجاد فائدة لهذا أيضًا. 🙏

**التكرار الأول:**

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

```

**الحل النهائي لحالتي الاستخدام المحددة:**

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

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/c/e/3/ce3959194572520e2466d5270f32837a8e6bc8e4.png)

---

_[View the full topic](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195)._