Dashboard Report - Post Emotion

data & reporting
, , , ,
1

This is an SQL version of the Dashboard Report for Post Emotion.

:discourse: This report requires the Discourse AI plugin and Sentiment Analysis to be enabled.

This dashboard report shows the number of posts classified with one of following emotions, group by poster trust level, within a specified date range:

  • Sadness
  • Surprise
  • Fear
  • Anger
  • Joy
  • Disgust
-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- int :threshold = 30

SELECT
    u.trust_level AS trust_level,
    COUNT(CASE WHEN (classification->>'sadness')::integer > :threshold THEN 1 ELSE NULL END) AS sadness,
    COUNT(CASE WHEN (classification->>'surprise')::integer > :threshold THEN 1 ELSE NULL END) AS surprise,
    COUNT(CASE WHEN (classification->>'fear')::integer > :threshold THEN 1 ELSE NULL END) AS fear,
    COUNT(CASE WHEN (classification->>'anger')::integer > :threshold THEN 1 ELSE NULL END) AS anger,
    COUNT(CASE WHEN (classification->>'joy')::integer > :threshold THEN 1 ELSE NULL END) AS joy,
    COUNT(CASE WHEN (classification->>'disgust')::integer > :threshold THEN 1 ELSE NULL END) AS disgust
FROM
    classification_results AS cr
    INNER JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
    INNER JOIN users u ON p.user_id = u.id
    INNER JOIN topics t ON t.id = p.topic_id
WHERE
    t.archetype = 'regular' AND
    p.user_id > 0 AND
    cr.model_used = 'emotion' AND
    (p.created_at > :start_date AND p.created_at < :end_date)
GROUP BY
    u.trust_level

SQL Query Explanation

The SQL query operates by performing the following steps:

  • Parameter Definition:
    • :start_date and :end_date to specify the date range for the analysis.
    • :threshold to set the minimum score for classifying sentiment emotion on posts. The default value for the :threshold is set to 30 to match the dashboard report.
  • Data Selection and Joining:
    • The query selects data from the classification_results table, which contains results from an emotion classification model applied to posts.
    • It joins the classification_results table with the posts table to filter only those classifications that belong to posts (cr.target_type = 'Post').
    • It further joins with the users and topics tables to access user trust levels and ensure the posts are part of regular topics (not private messages or other special types).
  • Filtering:
    • The query filters posts created within the specified date range (p.created_at > :start_date AND p.created_at < :end_date).
    • It ensures that the posts are from regular topics (t.archetype = 'regular'), made by registered users (p.user_id > 0), and specifically targets emotional classifications (cr.model_used = 'emotion').
  • Classification Counting:
    • For each emotion (sadness, surprise, fear, anger, joy, disgust), the query counts the number of posts classified with an intensity greater than the specified threshold (:threshold).
  • Grouping: The results are grouped by the trust level of the users (u.trust_level), providing a breakdown of emotional content by user trust level.

Example Results

trust_level sadness surprise fear anger joy disgust
0 14 13 3 2 13 0
1 64 83 16 5 63 3
2 105 334 51 15 220 2
3 28 109 18 9 44 5
4 60 193 63 2 136 5
2 Likes