# Dashboard Report - Overall Sentiment
**URL:** https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428
**Category:** Data & reporting
**Tags:** ai, sql-query, dashboard-reports, ai-sentiment, dashboard-sql
**Created:** [February 15, 2024, 1:24am UTC](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428 "2024-02-15T01:24:10Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [February 15, 2024, 1:24am UTC](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428/1 "2024-02-15T01:24:10Z")
This is an SQL version of the Dashboard Report for Overall Sentiment.
> :discourse: This report requires the [Discourse AI](https://meta.discourse.org/t/discourse-ai/259214) plugin and [Sentiment Analysis](https://meta.discourse.org/t/enable-sentiment-analysis/259599) to be enabled.
This dashboard report compares the number of posts classified either positive or negative with the “Sentiment” AI, over a specified period.
By leveraging sentiment analysis, the report categorizes posts as either positive or negative based on a defined threshold, which can be adjusted. This categorization is then used to calculate the overall sentiment for each day within the given date range. This report is particularly useful for understanding the general mood or sentiment of the community discussions during specific times.
```sql
-- [params]
-- date :start_date = 2024-01-13
-- date :end_date = 2024-02-14
-- double :threshold = 0.60
SELECT
DATE_TRUNC('day', p.created_at)::DATE AS date,
COUNT(CASE WHEN (classification->>'positive')::float > :threshold THEN 1 ELSE NULL END) AS positive_sentiment_posts,
COUNT(CASE WHEN (classification->>'negative')::float > :threshold THEN 1 ELSE NULL END) AS negative_sentiment_posts,
COUNT(CASE WHEN (classification->>'positive')::float > :threshold THEN 1 ELSE NULL END) - COUNT(CASE WHEN (classification->>'negative')::float > :threshold THEN 1 ELSE NULL END) AS overall_sentiment
FROM
classification_results AS cr
INNER JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
INNER JOIN topics t ON t.id = p.topic_id
INNER JOIN categories c ON c.id = t.category_id
WHERE
t.archetype = 'regular' AND
p.user_id > 0 AND
cr.model_used = 'cardiffnlp/twitter-roberta-base-sentiment-latest' AND
(p.created_at > :start_date AND p.created_at < :end_date)
GROUP BY
DATE_TRUNC('day', p.created_at)
```
### SQL Query Explanation
The SQL query operates by performing the following steps:
- **Parameter Definition** : It starts by defining three parameters:
- `:start_date` and `:end_date` to specify the date range for the analysis.
- `:threshold` to set the minimum score for classifying sentiments as positive or negative. The default value for the `:threshold` is set to 60 to match the dashboard report.
- **Data Selection and Joining** :
- The query selects data from the `classification_results` table, which stores the results of sentiment analysis on posts.
- It joins the `classification_results` with the `posts` table to filter posts based on their creation date and ensures the analysis is only for posts (`cr.target_type = 'Post'`).
- Further joins with the `topics` and `categories` tables ensure the analysis is limited to regular posts in specific categories.
- **Sentiment Classification** :
- For each post, it checks the sentiment score from the `classification_results` table. If the score for positive sentiment is greater than the threshold, it counts the post as positive. Similarly, it counts a post as negative if its negative sentiment score exceeds the threshold.
- **Aggregation** :
- The query then groups the results by day, based on the `created_at` timestamp of each post. For each day, it calculates:
- The total number of positive posts.
- The total number of negative posts.
- The overall sentiment, which is the difference between the total number of positive and negative posts.
### Example Results
| date | positive\_sentiment\_posts | negative\_sentiment\_posts | overall\_sentiment |
| --- | --- | --- | --- |
| 2024-01-13 | 10 | 21 | -11 |
| 2024-01-14 | 11 | 20 | -9 |
| 2024-01-15 | 23 | 7 | 16 |
| 2024-01-16 | 27 | 10 | 17 |
| 2024-01-17 | 47 | 22 | 25 |
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [May 24, 2024, 9:28am UTC](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428/2 "2024-05-24T09:28:47Z")
9 posts were split to a new topic: [Problems with Sentiment Backfill](https://meta.discourse.org/t/problems-with-sentiment-backfill/309270)
---
### Author:  [@SStrong](https://meta.discourse.org/u/SStrong)
#### Post date: [July 3, 2025, 9:27am UTC](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428/3 "2025-07-03T09:27:05Z")
> [@SaraDev](#):
>
> ```plaintext
> -- [params]
> -- date :start_date = 2024-01-13
> -- date :end_date = 2024-02-14
> -- double :threshold = 0.60
>
> SELECT
> DATE_TRUNC('day', p.created_at)::DATE AS date,
> COUNT(CASE WHEN (classification->>'positive')::float > :threshold THEN 1 ELSE NULL END) AS positive_sentiment_posts,
> COUNT(CASE WHEN (classification->>'negative')::float > :threshold THEN 1 ELSE NULL END) AS negative_sentiment_posts,
> COUNT(CASE WHEN (classification->>'positive')::float > :threshold THEN 1 ELSE NULL END) - COUNT(CASE WHEN (classification->>'negative')::float > :threshold THEN 1 ELSE NULL END) AS overall_sentiment
> FROM
> classification_results AS cr
> INNER JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
> INNER JOIN topics t ON t.id = p.topic_id
> INNER JOIN categories c ON c.id = t.category_id
> WHERE
> t.archetype = 'regular' AND
> p.user_id > 0 AND
> cr.model_used = 'cardiffnlp/twitter-roberta-base-sentiment-latest' AND
> (p.created_at > :start_date AND p.created_at < :end_date)
> GROUP BY
> DATE_TRUNC('day', p.created_at)
>
> ```
I’ve tried running this, getting 0 results however we do have sentiment analysis and AI ticket, can see it in that front end report but not from this query
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [July 3, 2025, 8:19pm UTC](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428/4 "2025-07-03T20:19:14Z")
Hi Sophie,
When did you enable the sentiment analysis on your site?
If you chose February 2024 as an end date in this query, was the analysis enabled before that?
With AI sentiment, only new posts are classified since the date that the analysis was enabled, so if you didn’t have it enabled before the dates you chose in the query, I’d expect it to return 0 results.
You may need to adjust the following parameters in the query to match the dates that you’re looking to view sentiment on your site:
```sql
-- date :start_date = 2024-01-13
-- date :end_date = 2024-02-14
```