# 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:** [27.Январь.2016 05:55:21 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:** 1
**Showing post:** 4

<div class="post-metadata">

### Author: ![DeanMarkTaylor](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/deanmarktaylor/32/102462_2.png) [@DeanMarkTaylor](https://meta.discourse.org/u/DeanMarkTaylor)
#### Post date: [10.Апрель.2016 02:50:43 UTC](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556/4 "2016-04-10T02:50:43Z")

</div>

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

```

---

_[View the full topic](https://meta.discourse.org/t/sql-the-most-n-used-words-per-user-speak-their-language/38556)._