שאילת פתרונות מקובלים

קהילה נתונים ודיווח
,

סיכום מבוסס בינה מלאכותית

Shreyas_Kulkarni is querying for accepted solutions data and found discrepancies between their SQL query results and the Accepted Solutions Dashboard Report. They were only seeing data up to March and incorrect counts. SaraDev explained that the original query used the outdated posts_custom_fields table, while accepted solutions are now tracked in the discourse_solved_solved_topics table since March :slightly_smiling_face:. SaraDev provided a corrected SQL query utilizing discourse_solved_solved_topics to accurately match the report data, including formatting and solution counts for all months in 2025. Shreyas_Kulkarni thanked SaraDev and will test the updated query once they have access to the discourse_solved_solved_topics table in Snowflake.

1

היי, אני משתמש בשאילתה הבאה כדי לבדוק פתרונות מקובלים החודש. עם זאת, איני רואה נתונים מעבר למרץ. בנוסף, המספרים הללו אינם תואמים למה שמוצג בדוח הפתרונות המקובלים. האם אוכל לקבל הדרכה כאן לגבי מה שאני עושה לא נכון?

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:

3

תודה @SaraDev! זה מועיל. אבדוק את הפלט של השאילתה הזו ברגע שאוכל לקבל את discourse_solved_solved_topics ב-Snowflake כדי לבצע שאילתה.