Usage of emojis

data & reporting
,
1

I’m planning to replace retort with discourse reactions. To find out which emojis to use, I wanted to know how their usage in my forum is. As I couldn’t find a query for this I share mine:

Count emojis added with the retort plugin

SELECT count(*), REGEXP_MATCHES(key, '([a-z_+]+)|retort') emoji
    FROM post_details
    WHERE extra = 'retort'
    GROUP BY key
    ORDER BY 1 DESC

Count emojis in posts (without the system user)

SELECT count(*), REGEXP_MATCHES(raw, ' :([a-z_]+): ', 'g') emoji
    FROM posts
    WHERE user_id > 0
    GROUP BY emoji
    ORDER BY 1 DESC
4 Likes
2

Nice. :slight_smile: I’ve not used the REGEXP_MATCHES before, but I’ve just given these a test drive (well, the second one. I don’t have Retort installed :slight_smile:).

I think you want to GROUP BY emoji rather than smiley. Also user_id > 1 would exclude the original admin. That may be intended, but just thought I’d mention it. :+1:

I also asked Bert :robot: for an explanation too if anyone else would find it useful:

What Bert said:

This query is used to count the number of times each emoji is used in the posts table.

Here’s a breakdown of the query:

  • SELECT count(*), REGEXP_MATCHES(raw, ' :([a-z_]+): ', 'g') emoji: This is the main part of the query. It’s counting the number of rows (count(*)) and using a regular expression (REGEXP_MATCHES) to match and extract the emoji from the raw column. The regular expression ' :([a-z_]+): ' is looking for patterns that start and end with a colon (:) and have one or more lowercase letters or underscores in between. The ‘g’ flag means it will match globally, i.e., find all matches rather than stopping after the first match. The matched emoji is then aliased as emoji.
  • FROM posts: This specifies the table from which the data is being retrieved, in this case, the posts table.
  • WHERE user_id > 0: This is a condition that filters the rows being considered. Only rows where the user_id is greater than 0 are included.
  • GROUP BY emoji: This groups the results by the emoji alias. This means that for each unique emoji, it will count the number of rows.
  • ORDER BY 1 DESC: This orders the results by the first column in the result set (which is the count) in descending order. This means the emojis used most frequently will appear first.

So, in summary, this query is counting the number of times each emoji is used in the posts table, for posts where the user_id is greater than 0, and ordering the results so that the most frequently used emojis appear first.

2 Likes
3

Oh, you are right. I changed it on my site but forgot here. :blush:

Indeed, I should have mentioned that in my post. user_id 1 is a legacy user (when anonymous posting was still allowed on my site) which I didn’t want to include in the count.

1 Like