Hi @tknospdr,
To answer both your questions here:
You can use the below query to address this:
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
-- null category_id :category_id
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names,
c.name AS category_name,
t.category_id
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name, t.category_id
),
solved_topics AS (
SELECT
dsst.topic_id,
MIN(dsst.created_at) AS first_solution_at, -- Get earliest solution
MAX(dsst.created_at) AS latest_solution_at -- Get latest solution
FROM discourse_solved_solved_topics dsst
GROUP BY dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.topic_id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.first_solution_at, 'YYYY-MM-DD'), '') AS first_solution_create,
COALESCE(TO_CHAR(st.latest_solution_at, 'YYYY-MM-DD'), '') AS latest_solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.first_solution_at::date - vt.created_at::date, 0) AS "time_to_first_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.first_solution_at - vt.created_at)) / 3600.00), 0) AS "time_to_first_solution(hours)",
COALESCE(st.latest_solution_at::date - vt.created_at::date, 0) AS "time_to_latest_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.latest_solution_at - vt.created_at)) / 3600.00), 0) AS "time_to_latest_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
CASE
WHEN st.topic_id IS NULL THEN vt.total_days
ELSE COALESCE(st.latest_solution_at::date - vt.created_at::date, 0)
END AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.topic_id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
AND (:category_id ISNULL OR vt.category_id = :category_id)
GROUP BY st.topic_id, st.first_solution_at, st.latest_solution_at, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
Where the -- null category_id :category_id parameter can be used to (optionally) select a category to run the report for, and the results track both the first and latest solutions.
Additionally, the the total_days_without_solution result will now use the latest solution date instead of the first one.