-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
クエリが遡る期間を調整したい場合は、クエリのこの行を変更するだけです。
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
パラメータを削除したい場合は、代わりに次のようなクエリを使用することもできます。
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
上記で共有された Topics with No Response クエリの interval パラメータに基づいた別の例を次に示します。
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
