# Dashboard Report - Accepted Solutions

**URL:** https://meta.discourse.org/t/dashboard-report-accepted-solutions/288863
**Category:** Data & reporting
**Tags:** solved, sql-query, dashboard-reports, dashboard-sql
**Created:** [December 15, 2023, 9:15pm UTC](https://meta.discourse.org/t/dashboard-report-accepted-solutions/288863 "2023-12-15T21:15:50Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [December 15, 2023, 9:15pm UTC](https://meta.discourse.org/t/dashboard-report-accepted-solutions/288863/1 "2023-12-15T21:15:51Z")

</div>

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.

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

WITH daily_solutions AS (
  SELECT 
    DATE(ta.created_at) AS solution_date,
    COUNT(*) AS solution_count
  FROM discourse_solved_solved_topics st
  JOIN discourse_solved_topic_answers ta ON ta.solved_topic_id = st.id
  JOIN posts p ON p.id = ta.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(ta.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 and the `discourse_solved_topic_answers` table which was introduced in the [Discourse PR #39806](https://github.com/discourse/discourse/pull/39806).

- **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 |
| … | … |