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
  • Admiration
  • Amusement
  • Annoyance
  • Approval
  • Caring
  • Confusion
  • Curiosity
  • Desire
  • Disappointment
  • Disapproval
  • Embarrassment
  • Excitement
  • Gratitude
  • Grief
  • Love
  • Nervousness
  • Neutral
  • Optimism
  • Pride
  • Realization
  • Relief
  • Remorse
-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- double :threshold = 0.30

SELECT
    u.trust_level AS trust_level,
    -- Basic emotions from original query
    COUNT(CASE WHEN (classification->>'sadness')::float > :threshold THEN 1 ELSE NULL END) AS sadness,
    COUNT(CASE WHEN (classification->>'surprise')::float > :threshold THEN 1 ELSE NULL END) AS surprise,
    COUNT(CASE WHEN (classification->>'fear')::float > :threshold THEN 1 ELSE NULL END) AS fear,
    COUNT(CASE WHEN (classification->>'anger')::float > :threshold THEN 1 ELSE NULL END) AS anger,
    COUNT(CASE WHEN (classification->>'joy')::float > :threshold THEN 1 ELSE NULL END) AS joy,
    COUNT(CASE WHEN (classification->>'disgust')::float > :threshold THEN 1 ELSE NULL END) AS disgust,
    
    -- Additional emotions from the second query
    COUNT(CASE WHEN (classification->>'admiration')::float > :threshold THEN 1 ELSE NULL END) AS admiration,
    COUNT(CASE WHEN (classification->>'amusement')::float > :threshold THEN 1 ELSE NULL END) AS amusement,
    COUNT(CASE WHEN (classification->>'annoyance')::float > :threshold THEN 1 ELSE NULL END) AS annoyance,
    COUNT(CASE WHEN (classification->>'approval')::float > :threshold THEN 1 ELSE NULL END) AS approval,
    COUNT(CASE WHEN (classification->>'caring')::float > :threshold THEN 1 ELSE NULL END) AS caring,
    COUNT(CASE WHEN (classification->>'confusion')::float > :threshold THEN 1 ELSE NULL END) AS confusion,
    COUNT(CASE WHEN (classification->>'curiosity')::float > :threshold THEN 1 ELSE NULL END) AS curiosity,
    COUNT(CASE WHEN (classification->>'desire')::float > :threshold THEN 1 ELSE NULL END) AS desire,
    COUNT(CASE WHEN (classification->>'disappointment')::float > :threshold THEN 1 ELSE NULL END) AS disappointment,
    COUNT(CASE WHEN (classification->>'disapproval')::float > :threshold THEN 1 ELSE NULL END) AS disapproval,
    COUNT(CASE WHEN (classification->>'embarrassment')::float > :threshold THEN 1 ELSE NULL END) AS embarrassment,
    COUNT(CASE WHEN (classification->>'excitement')::float > :threshold THEN 1 ELSE NULL END) AS excitement,
    COUNT(CASE WHEN (classification->>'gratitude')::float > :threshold THEN 1 ELSE NULL END) AS gratitude,
    COUNT(CASE WHEN (classification->>'grief')::float > :threshold THEN 1 ELSE NULL END) AS grief,
    COUNT(CASE WHEN (classification->>'love')::float > :threshold THEN 1 ELSE NULL END) AS love,
    COUNT(CASE WHEN (classification->>'nervousness')::float > :threshold THEN 1 ELSE NULL END) AS nervousness,
    COUNT(CASE WHEN (classification->>'neutral')::float > :threshold THEN 1 ELSE NULL END) AS neutral,
    COUNT(CASE WHEN (classification->>'optimism')::float > :threshold THEN 1 ELSE NULL END) AS optimism,
    COUNT(CASE WHEN (classification->>'pride')::float > :threshold THEN 1 ELSE NULL END) AS pride,
    COUNT(CASE WHEN (classification->>'realization')::float > :threshold THEN 1 ELSE NULL END) AS realization,
    COUNT(CASE WHEN (classification->>'relief')::float > :threshold THEN 1 ELSE NULL END) AS relief,
    COUNT(CASE WHEN (classification->>'remorse')::float > :threshold THEN 1 ELSE NULL END) AS remorse,
    
    -- Total count of posts with any emotion above threshold
    COUNT(*) AS total_posts,
    
    -- Total count of posts with at least one emotion above threshold
    COUNT(CASE WHEN 
        (classification->>'sadness')::float > :threshold OR
        (classification->>'surprise')::float > :threshold OR
        (classification->>'fear')::float > :threshold OR
        (classification->>'anger')::float > :threshold OR
        (classification->>'joy')::float > :threshold OR
        (classification->>'disgust')::float > :threshold OR
        (classification->>'admiration')::float > :threshold OR
        (classification->>'amusement')::float > :threshold OR
        (classification->>'annoyance')::float > :threshold OR
        (classification->>'approval')::float > :threshold OR
        (classification->>'caring')::float > :threshold OR
        (classification->>'confusion')::float > :threshold OR
        (classification->>'curiosity')::float > :threshold OR
        (classification->>'desire')::float > :threshold OR
        (classification->>'disappointment')::float > :threshold OR
        (classification->>'disapproval')::float > :threshold OR
        (classification->>'embarrassment')::float > :threshold OR
        (classification->>'excitement')::float > :threshold OR
        (classification->>'gratitude')::float > :threshold OR
        (classification->>'grief')::float > :threshold OR
        (classification->>'love')::float > :threshold OR
        (classification->>'nervousness')::float > :threshold OR
        (classification->>'neutral')::float > :threshold OR
        (classification->>'optimism')::float > :threshold OR
        (classification->>'pride')::float > :threshold OR
        (classification->>'realization')::float > :threshold OR
        (classification->>'relief')::float > :threshold OR
        (classification->>'remorse')::float > :threshold
    THEN 1 ELSE NULL END) AS emotional_posts
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 = 'SamLowe/roberta-base-go_emotions' AND
    (p.created_at > :start_date AND p.created_at < :end_date)
GROUP BY
    u.trust_level
ORDER 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 0.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 admiration amusement … emotional_posts total_posts
0 12 8 5 15 20 3 18 25 … 78 120
1 35 42 18 29 64 12 57 82 … 245 310
2 67 85 32 48 112 23 124 156 … 487 520
3 45 63 24 37 95 18 102 124 … 326 380
4 21 36 14 18 53 9 67 72 … 175 210
3 Likes