Most used word per article

3 Ways To Filter On Order In PostgreSQL

How to filter on order in PostgreSQL and SQLAlchemy

Not a Medium member? Use the Friend Link to read this article!

Be it to remove duplicates at query time or simply to retrieve the most/least recent (used/ordered/etc) attribute, we implement queries which filter records based on order. Here are 3 implementations in PostgreSQL.

The Data

In a table, fastapi_article , records about my articles are stored, with their content and their text stats (Medium article).

In a materialized view, term_occurrence_per_article_mv, per article and word, the number of times the word appears in the article are stored:

create materialized view term_occurrence_per_article_mv as
select
    article_id,
    word,
    nentry as number_of_occurrences
from fastapi_article,
    ts_stat('select article_content_simple_with_no_stop_words from fastapi_article where article_id = ' || '''' ||
                  fastapi_article.article_id || '''' || '::uuid')

Getting The Most Used Word Per Article With Row Number And Filter

With the row_number window function we can calculate the order of the word in an article based on the descending number of…