This is an SQL version of the Dashboard Report for Reactions.
This report provides a daily count of all likes and reactions to posts across a site over a specified date range.
This report helps provide a snapshot of user engagement within a Discourse community by measuring the frequency of various reaction emojis and likes on posts. By analyzing the usage of different reaction emojis, administrators can gain insights into how often users interact with content, users’ emotional responses to posts, and identify both popular and underused reaction emojis.
This report requires the Discourse Reactions plugin to be enabled on your site. The reactions available on the report will depend on which specific reactions have been enabled with the
discourse_reactions_enabled_reactions
site setting.
-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
SELECT
r.day,
COALESCE(l.likes_count, 0) as likes_count,
sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
SELECT
date_trunc('day', drru.created_at)::date as day,
drr.reaction_value,
count(drru.id) as reactions_count
FROM discourse_reactions_reactions as drr
LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
WHERE drr.reaction_users_count IS NOT NULL
AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
SELECT
count(pa.id) as likes_count,
date_trunc('day', pa.created_at)::date as day
FROM post_actions as pa
WHERE pa.post_action_type_id = 2
AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date
GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day
SQL Query Explanation
Parameters
- The query accepts two parameters,
:start_date
and:end_date
, which define the date range for the report. Both date parameters accept the date format ofYYYY-MM-DD
.
Query Structure
- Inner Query (Reactions): The query starts with a subquery that selects the date (
day
), the type of reaction (reaction_value
), and the count of reactions (reactions_count
) from thediscourse_reactions_reactions
table joined withdiscourse_reactions_reaction_users
on thereaction_id
. This join ensures we are accounting for user-specific reactions. - Aggregation by Reaction Types: The selected data is then grouped by
day
andreaction_value
, restricting the range to the selected start and end dates. This is used to calculate the total count of each type of reaction for each day in the specified date range. - Sorting Reaction Totals: For each type of reaction, the query uses a
CASE
statement to aggregate the number of times the specific reaction is used and casts the result as an integer for a clean count.- You may need to adjust the
reaction_value = '...'
in this section depending on which reactions are enabled on your site.
- You may need to adjust the
- Likes Count Subquery: A separate subquery is used to calculate the total number of likes (
likes_count
) for each day using thepost_actions
table wherepost_action_type_id
corresponds to likes. - Combining Data: The outer query then combines the counting of likes with the reaction counts by joining on the
day
field. - Final Selection: The outermost
SELECT
generates the final output with theday
, the number of likes (likes_count
), and counts of each reaction type. If there’s no data for likes on a specific day, theCOALESCE
function ensures a zero is displayed instead ofNULL
. - Ordering the Results: The results are ordered by date (
r.day
) to create a time series of engagement on the site.
Columns
day
: The date on which the reactions and likes were counted.likes_count
: Total number of likes for each day.- Each reaction type (laughing, cry, exploding_head, etc.): Individual columns showing the total count of each reaction type per day.
Example Results
day | likes_count | laughing | cry | exploding_head | clap | confetti_ball | hugs | chefs_kiss | one_hundred | plus_one | rocket | star_struck | eyes | discourse |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2023-12-16 | 13 | 0 | 3 | 0 | 3 | 1 | 0 | 0 | 0 | 5 | 2 | 2 | 1 | 0 |
2023-12-17 | 17 | 1 | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 4 | 4 | 1 | 2 | 0 |
2023-12-18 | 46 | 0 | 1 | 0 | 6 | 0 | 1 | 3 | 0 | 27 | 3 | 4 | 5 | 0 |