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. :information_source: 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 = :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 of YYYY-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 the discourse_reactions_reactions table joined with discourse_reactions_reaction_users on the reaction_id. This join ensures we are accounting for user-specific reactions. Aggregation by Reaction Types: The selected data is then grouped by day and reaction_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. Likes Count Subquery: A separate subquery is used to calculate the total number of likes (likes_count) for each day using the post_actions table where post_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 the day, the number of likes (likes_count), and counts of each reaction type. If there’s no data for likes on a specific day, the COALESCE function ensures a zero is displayed instead of NULL. 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

And heads up for us not-so-dev types… those reactions are hard coded there and what are in use here. That means too that if offered reactions has changed at some point this shows wrong older times. I have feeling this is counting wrong if default has changed from :heart: to :+1: for example.

[תמונה] Jakke Flemming: I have feeling this is counting wrong if default has changed from :heart: to :+1: for example. @JammyDodger fixed a query I was using that accounts for this, I believe: [תמונה] Which reactions are the most commonly-used in your community? Community Building Let’s slide this over to #community to get more eyes on it. eyes Though the query isn’t counting Likes properly, or at least not all of ours (possibly because we’ve turned Reactions on mid-flow) reaction posts count heart 52295 144915 +1 1167 1224 100 1101 1236 versus: reaction_value count heart 1371442 100 1236 +1 1224 play_button FWIW I've been using a small variation of this one to produce our table: There’s also the stock one in the reports section too to …

דוח לוח מחוונים - תגובות

קהילה נתונים ודיווח

jericson (Jon Ericson) 19 בינואר,‏ 2024,‏ 10:36pm 3

@JammyDodger fixed a query I was using that accounts for this, I believe:

נושא		תגובות	צפיות
Reports for Discourse Reaction plugin Data & reporting reactions	0	271	26 במרץ,‏ 2024
Dashboard Report - Likes Data & reporting sql-query , dashboard-reports , dashboard-sql	3	519	22 בינואר,‏ 2025
Which reactions are the most commonly-used in your community? Community Building reactions , sql-query	3	773	13 בדצמבר,‏ 2023
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	265	31 בדצמבר,‏ 2019
Dashboard Report - Daily Engaged Users Data & reporting sql-query , dashboard-reports , dashboard-sql	1	696	9 בדצמבר,‏ 2023

דוח לוח מחוונים - תגובות

נושאים קשורים