Dashboard Report - Accepted Solutions

Data & reporting
, , ,
1

This is an SQL version of the Dashboard Report for Accepted Solutions.

This report provides a count of posts that have been marked as solutions on a daily basis within a specified date range. It is useful for understanding how often users are finding answers to their questions and marking them as solutions over time.

-- [params]
-- date :start_date = 2025-03-01
-- date :end_date = 2025-04-01
-- null category_id :category_id

WITH daily_solutions AS (
  SELECT 
    DATE(st.created_at) AS solution_date,
    COUNT(*) AS solution_count
  FROM discourse_solved_solved_topics st
  JOIN posts p ON p.id = st.answer_post_id AND p.deleted_at IS NULL
  JOIN topics t ON t.id = st.topic_id 
                AND t.archetype <> 'private_message'
                AND t.deleted_at IS NULL
                AND (:category_id IS NULL OR t.category_id = :category_id)
  JOIN users u ON u.id = p.user_id
  WHERE 
    st.created_at BETWEEN :start_date AND :end_date
    AND u.id > 0
    AND u.active
    AND u.silenced_till IS NULL
    AND u.suspended_till IS NULL
  GROUP BY DATE(st.created_at)
)

SELECT 
  solution_date,
  solution_count
FROM daily_solutions
ORDER BY solution_date

SQL query explanation

This query uses the discourse_solved_solved_topics table introduced in the discourse-solved PR #352:

  • Parameters: The query accepts:
    • :start_date and :end_date parameters in YYYY-MM-DD format with default values
    • An optional :category_id to filter by specific category
  • Common Table Expression: Uses a CTE named daily_solutions to organize the logic
  • Joins:
    • posts - Ensures the solution post still exists and isn’t deleted
    • topics - Ensures the topic exists, isn’t a private message, and isn’t deleted
    • users - Verifies the solution poster is a valid user
  • Filtering: The WHERE clause:
    • Limits to the specified date range
    • Ensures users are active, not silenced, and not suspended
    • Allows optional category filtering
  • Grouping and Ordering: Groups by date and orders chronologically

The output of this query will be a list of dates with the corresponding count of posts marked as solutions on each of those dates.

Example Results

day accepted_solutions
2023-11-14 16
2023-11-15 5
2023-11-16 10
2023-11-17 4
2023-11-18 2
… …
2 Likes