# SQL: The most N used words per user (speak their language!)
**URL:** https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2016 年 1 月 27 日午前 5:55 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556 "2016-01-27T05:55:21Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 1 月 27 日午前 5:55 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/1 "2016-01-27T05:55:21Z")
By looking at the top 20..50 words used by a user, I hope to identify who cares about what the most, and then use the information to engage them in right topics and ask them right questions for better motivation.
What tables / columns would be useful for this task?
* * *
I suspect I should play with `post_search_data.search_data`, which is a `tsvector`, i.e. words and their frequencies per post. But if you think there’s a better way, please advise!
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [2016 年 1 月 27 日午前 9:01 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/2 "2016-01-27T09:01:40Z")
“`post_search_data.search_data`” is fine if you want to work on [**_lexemes_**](http://www.postgresql.org/docs/current/static/datatype-textsearch.html#DATATYPE-TSVECTOR) since
> a “`tsvector`” value is a sorted list of distinct _lexemes_, which are words that have been normalized to merge different variants of the same word.
If you want to work on _raw_ words, then it’s better to use “`posts.raw`” 😉
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 1 月 28 日午前 1:51 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/3 "2016-01-28T01:51:18Z")
So, here it is:
# “Speak their language” SQL query
This query fetches 60 lexemes that are most frequently used by users in their messages from last 90 days, per user.
### Notes
- users are ordered by the number of words they posted in the last 90 days, most active posters first
- only those messages are included that were posted in the last 90 days
- instead of counting word tokens, the query counts lexems, so that “dogs” and “dog” are interpreted as the same word
### Application
- **Control your language**. I noticed I use the word “flag” too often. Maybe not a good habit for an admin who tries to be friendly!
- **Find who cares about what the most**. For instance:
- From the screenshot, I can see that user `shcher86` used word `cheese` 517 times, `cottage cheese` 133 times and `recipe` 202 times. Now I know what to ask her about 😉
- Admin `Brovarchanka` really cares about `goats` (353), and also uses the `:wink:` smile all the time. I should care more about users and goats too, rather than flags!
* * *
### SQL Query
```sql
WITH
posters AS (
SELECT SUM(p.word_count) as total_words,
u.*
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.created_at > now()::date - 91
GROUP BY u.id
)
SELECT u.id as user_id,
(SELECT string_agg(word || ' - ' || nentry, ' ') FROM
(SELECT * FROM ts_stat('
SELECT search_data
FROM post_search_data psd
LEFT JOIN posts p ON p.id = psd.post_id
WHERE p.created_at > now()::date - 91
AND p.user_id = ' || u.id)
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 60
) freq
) as top_words
FROM posters u
ORDER BY u.total_words DESC
LIMIT 100
```
### Tuning
- `LIMIT 100` limits to the top 100 posters; adjust to your needs
- `p.created_at > now()::date - 91` limits analysis to posts in last 90 days:
91 = 90 days
90 = 89 days
… and so on
You have to change it in 2 places in the query
- `LIMIT 60` limits output to 60 lexemes, that’s where you may change it if needs be
* * *
Enjoy learning your users’ language!
P.S. And don’t ask your users to flag too often 😉
---
### Author:  [@DeanMarkTaylor](https://meta.discourse.org/u/DeanMarkTaylor)
#### Post date: [2016 年 4 月 10 日午前 2:50 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/4 "2016-04-10T02:50:43Z")
Well done and thanks @meglio, interesting results across a few Discourse instances.
I turned this into a parametrised query for [Data Explorer](https://meta.discourse.org/t/data-explorer-plugin/32566) (@riking awesome job on this plugin!)
[most-used-words-per-user.dcquery (1).json](https://global.discourse-cdn.com/meta/original/3X/f/f/ff4adb350105e6e4563de4000e0013e4e74fc294.json) (1.8 KB)
```SQL
-- [params]
-- int :past_x_days = 90
-- int :top_x_words = 60
-- int :top_x_users = 100
WITH posters AS (
SELECT
sum (p.word_count ) AS total_words,
u.*
FROM
users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE
p.created_at > now() ::date - ( :past_x_days + 1 )
GROUP BY
u.id
)
SELECT
u.id AS user_id,
(
SELECT
string_agg ( word || ' - ' || nentry, ' ' )
FROM (
SELECT
*
FROM
ts_stat ('
SELECT search_data FROM post_search_data psd
LEFT JOIN posts p
ON p.id = psd.post_id
WHERE p.created_at > now()::date - ' || (
:past_x_days + 1 )
|| ' AND p.user_id = ' || u.id
)
ORDER BY
nentry DESC,
ndoc DESC,
word
LIMIT :top_x_words
)
AS freq
)
AS top_words
FROM
posters AS u
ORDER BY
u.total_words DESC
LIMIT :top_x_users
```
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016 年 4 月 10 日午前 5:30 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/5 "2016-04-10T05:30:59Z")
A really cool query, but I’ve noticed some unexpected results.
Words from the Category and Topic tiles are being included in top\_words, 1 time for each post made in the topic.
In other words, if the category is “ABC” and the topic is “XYZ” if a member makes 25 posts in various topics in the ABC category including 10 in the XXZ topic, even if they never made a post with either ABC or XYZ in it, the count will show as
`ABC - 25, XYZ - 10`
On a positive note, words inside quotes, img tags and links don’t appear to be included.
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 4 月 10 日午前 6:24 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/6 "2016-04-10T06:24:27Z")
I don’t extract words on my own - I use topic search data vector generated in PostgreSQL that Discourse builds for search purposes.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016 年 4 月 10 日午前 6:34 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/7 "2016-04-10T06:34:13Z")
I’ve been messing with queries using posts.raw and posts.cooked but it’s tricky business to not include words in quotes, image tags etc. i.e. non-posted words - Note, this is a very buggy example
`regexp_matches(posts.raw, '\[quote(?:[^]])*\][^[]*\[\/quote]', 'g')`
I’m thinking it might be easier to subtract category and title words from those found in the post\_search\_data table.
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 4 月 10 日午前 6:36 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/8 "2016-04-10T06:36:11Z")
So, do you think the words from category name and title are automatically added to `post_search_data `? Hm that’s not good. Yes we should extract them.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016 年 4 月 10 日午前 7:08 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/9 "2016-04-10T07:08:19Z")
> [@meglio](#):
>
> the words from category name and title are automatically added to post\_search\_data?
As an example this for the default “Staff About”
**search\_data**
`'admin':11 'categori':2,17 'discuss':5 'moder':13 'privat':1 'staff':4,16,18 'topic':6 'visibl':9`
**raw\_data**
> Private category for staff discussions. Topics are only visible to admins and moderators. About the Staff category Staff
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 4 月 10 日午前 11:02 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/10 "2016-04-10T11:02:31Z")
So, I can’t see the “about” word in the data (Staff **About** ).
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016 年 4 月 10 日午後 6:53 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/11 "2016-04-10T18:53:50Z")
Yes, there are a few words that I guess aren’t included because they aren’t considered helpful as search terms.
I can see the logic for not including “for”, “are”, “only”, “to”, “and”, “the” and the like.
But I am a bit surprised “about” isn’t considered a word that might be searched for
---
### Author:  [@DeanMarkTaylor](https://meta.discourse.org/u/DeanMarkTaylor)
#### Post date: [2016 年 4 月 10 日午後 7:17 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/12 "2016-04-10T19:17:57Z")
List of stop words here for reference:
[https://apt-browse.org/browse/ubuntu/trusty/main/i386/postgresql-9.3/9.3.4-1/file/usr/share/postgresql/9.3/tsearch\_data/english.stop](https://apt-browse.org/browse/ubuntu/trusty/main/i386/postgresql-9.3/9.3.4-1/file/usr/share/postgresql/9.3/tsearch_data/english.stop)
The idea is that these words appear in “most” English “texts” regardless of subject.
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 4 月 11 日午前 1:52 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/13 "2016-04-11T01:52:48Z")
@Mittineague, back to your example, I still can’t understand where is the issue.
There are only 3 indexes for the word `staff`:
`'staff':4,16,18`
They can all be found in the text:
> Private category for **staff** discussions. Topics are only visible to admins and moderators. About the **Staff** category **Staff**
No “staff” word is taken from “Staff About”.
Or did I misinterpret your explanations?
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016 年 4 月 11 日午前 2:50 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/14 "2016-04-11T02:50:08Z")
lol That’s what I get for being lazy and typing only “Staff About” in hopes you would know what I meant.
The category is - Staff (18)
The topic title is - About the Staff category (14-17)
The post content is - Private category for staff discussions. Topics are only visible to admins and moderators. (1-13)
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 4 月 11 日午前 3:04 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/15 "2016-04-11T03:04:40Z")
Thanks, now it makes sense. So, the query is useless as is unless it is fixed to exclude “category keywords” from the statistics.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016 年 4 月 11 日午前 3:17 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/16 "2016-04-11T03:17:22Z")
I wouldn’t say it’s entirely useless. Only that the results are corrupt.
I was thinking of trying some kind of query like
word count = word count - ((category title + topic title words) x number of posts in topic)
but my Postgres chops aren’t all that great so it will take me some time if ever to come up with it.
Still, I like using post\_search\_data over posts as I think it would be easier to deal with the cat and topic titles than it would be to deal with quotes, images, links etc. that are in raw / cooked
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 4 月 11 日午前 3:22 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/17 "2016-04-11T03:22:08Z")
The solution would be to create a ts\_vector from category title and topic title and subtract that vector from the result, per-topic.
---
### Author:  [@saori](https://meta.discourse.org/u/saori)
#### Post date: [2016 年 5 月 19 日午前 5:51 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/18 "2016-05-19T05:51:14Z")
How can I change my website to led users see the search results of these words?
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [2016 年 5 月 19 日午前 6:06 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/19 "2016-05-19T06:06:41Z")
Just in case you’d like to show the query results in wordpress:
> [@Display Data Explorer query results in WordPress (with TwigAnything)](https://meta.discourse.org/t/tutorial-display-data-explorer-query-results-in-wordpress-with-twiganything-1-6-3/40956):
>
> [Data Explorer](https://meta.discourse.org/t/data-explorer-plugin/32566) plugin lets us extract and explore about any data from Discourse. In this tutorial I’ll show you how to display it in WordPress, like this: The data can be displayed in posts/pages, widgets or in Visual Composer blocks The data is optionally cached in WordPress You have full control on how data is output. In this tutorial we will use [Twig Anything](https://meta.discourse.org/t/announcing-wordpress-plugin-to-display-any-data-from-discourse/30968) WordPress plugin (I’m the plugin author) Use-case So, let’s build a list of topics from particular categories published in…
---
### Author:  [@DiscourseMetrics](https://meta.discourse.org/u/DiscourseMetrics)
#### Post date: [2019 年 5 月 17 日午後 8:50 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/20 "2019-05-17T20:50:42Z")
Unsure if I should start a new topic or not but.. would it be possible to tweak this SQL to get a list of the X most common words used in the last Y posts, regardless of user? I’m trying to look for common technical words I can use to create a dictionary on my forum.
[次のページ](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556.md?page=2)