Accepted Solutions Query

Data & reporting
,
1

Hi, I am using the following query to check accepted solutions this month. However, I do not see data past March. Additionally, these numbers do not match what is shown on the Accepted solutions Report. Could I get some guidance here on what I’m doing wrong?

SELECT DISTINCT 
YEAR(p.created_at), MONTH(p.created_at), 
count(distinct(p.topic_id))
    FROM posts p
    JOIN posts_custom_fields pcf ON pcf.post_id = p.id
    WHERE (pcf.name = 'is_accepted_answer' AND pcf.value = 'true') AND YEAR(p.created_at) = 2025
    GROUP BY 1,2
2

Hi Shreyas,

Looking at the query you’ve shared, the reason you’re not seeing data past March and that the numbers in your query don’t match the Accepted Solutions Dashboard Report is because you’re using the posts_custom_fields database table, however, the Discourse Solved plugin tracks accepted solutions in using discourse_solved_solved_topics table.

As of March, 2025, there was a change that updated the Discourse Solved plugin to use the discourse_solved_solved_topics database table to determine solved topics on your Discourse site, and this table is now the source of truth for all solution data.

Here’s a corrected version of your query that will match the Accepted Solutions Report, and show you data for 2025.

Accepted solutions by month for 2025

SELECT 
    EXTRACT(YEAR FROM p.created_at) AS year,
    EXTRACT(MONTH FROM p.created_at) AS month,
    TO_CHAR(p.created_at, 'Month') AS month_name,
    COUNT(DISTINCT dst.topic_id) AS solutions_count
FROM discourse_solved_solved_topics dst
JOIN posts p ON p.id = dst.answer_post_id
WHERE p.created_at >= '2025-01-01' AND p.created_at < '2026-01-01'
GROUP BY 1, 2, 3
ORDER BY year, month

The results for this query will include:

  • All months in 2025 that have accepted solutions (not just March)
  • Numbers that match the Accepted Solutions Report because it’s using the same underlying database table
  • Date formatting using PostgreSQL’s native functions

I hope this helps you find the data you’re looking for! :slightly_smiling_face:

1 Like