word_count_with_stopwords_english_postgres.sql

select * from (

select

     regexp_split_to_table(lower(post_text), '\s+') as word
    , count(1) as word_count
from
    (select post_text from 
-- subquery, used to recieve what part of the medium database you want.
(select mpl.user_username,mps.article_url,mps.image_count,post_tags,recommends,reading_time,mps.title,mptxt.text post_text,count(*) link_count from medium_posts_links mpl

inner join medium_posts_stats mps on mps.post_id = mpl.post_id
inner join medium_posts_tags mpt on mpt.post_id = mpl.post_id
inner join medium_posts_text mptxt on mptxt.post_id = mpl.post_id

where mpt.tag_name ilike 'Artificial Intelligence' or mpt.tag_name ilike 'Machine Learning' or mpt.tag_name ilike 'Deep Learning'
group by mpl.post_id,mpl.user_username,article_url,mps.image_count,mps.post_tags,mps.recommends,mps.reading_time,mps.title,mptxt.text
order by CAST(recommends as decimal) desc) subquery

) s
group by
word

order by word_count desc
) s where word not in ('a’s','ain’t','aren’t','c’mon','c’s','can’t','couldn’t','didn’t','doesn’t','don’t','hadn’t','hasn’t','haven’t','he’s','here’s','i’d','i’ll','i’m','i’ve','isn’t','it’d','it’ll','it’s','let’s','shouldn’t','t’s','that’s','there’s','they’d','they’ll','they’re','they’ve','wasn’t','we’d','we’ll','we’re','we’ve','weren’t','what’s','where’s','who’s','won’t','wouldn’t','you’d','you’ll','you’re','you’ve','da','ve',':','zu','a','de','i','o','und','e','der','em','ich','um','na','wir','es','als','é','able','about','above','according','accordingly','across','actually','after','afterwards','again','against','all','allow','allows','almost','alone','along','already','also','although','always','am','among','amongst','an','and','another','any','anybody','anyhow','anyone','anything','anyway','anyways','anywhere','apart','appear','appreciate','appropriate','are','around','as','aside','ask','asking','associated','at','available','away','awfully','be','became','because','become','becomes','becoming','been','before','beforehand','behind','being','believe','below','beside','besides','best','better','between','beyond','both','brief','but','by','came','can','cannot','cant','cause','causes','certain','certainly','changes','clearly','co','com','come','comes','concerning','consequently','consider','considering','contain','containing','contains','corresponding','could','course','currently','definitely','described','despite','did','different','do','does','done','down','downwards','during','each','edu','eg','eight','either','else','elsewhere','enough','entirely','especially','et','etc','even','ever','every','everybody','everyone','everything','everywhere','ex','exactly','example','except','far','few','fifth','first','five','followed','following','follows','for','former','formerly','forth','four','from','further','furthermore','get','gets','getting','given','gives','go','goes','going','gone','got','gotten','greetings','had','happens','hardly','has','have','having','he','hello','help','hence','her','here','hereafter','hereby','herein','hereupon','hers','herself','hi','him','himself','his','hither','hopefully','how','howbeit','however','ie','if','ignored','immediate','in','inasmuch','inc','indeed','indicate','indicated','indicates','inner','insofar','instead','into','inward','is','it','its','itself','just','keep','keeps','kept','know','known','knows','last','lately','later','latter','latterly','least','less','lest','let','like','liked','likely','little','look','looking','looks','ltd','mainly','many','may','maybe','me','mean','meanwhile','merely','might','more','moreover','most','mostly','much','must','my','myself','name','namely','nd','near','nearly','necessary','need','needs','neither','never','nevertheless','new','next','nine','no','nobody','non','none','noone','nor','normally','not','nothing','novel','now','nowhere','obviously','of','off','often','oh','ok','okay','old','on','once','one','ones','only','onto','or','other','others','otherwise','ought','our','ours','ourselves','out','outside','over','overall','own','particular','particularly','per','perhaps','placed','please','plus','possible','presumably','probably','provides','que','quite','qv','rather','rd','re','really','reasonably','regarding','regardless','regards','relatively','respectively','right','said','same','saw','say','saying','says','second','secondly','see','seeing','seem','seemed','seeming','seems','seen','self','selves','sensible','sent','serious','seriously','seven','several','shall','she','should','since','six','so','some','somebody','somehow','someone','something','sometime','sometimes','somewhat','somewhere','soon','sorry','specified','specify','specifying','still','sub','such','sup','sure','take','taken','tell','tends','th','than','thank','thanks','thanx','that','the','their','theirs','them','themselves','then','thence','there','thereafter','thereby','therefore','therein','theres','thereupon','these','they','think','third','this','thorough','thoroughly','those','though','three','through','throughout','thru','thus','to','together','too','took','toward','towards','tried','tries','truly','try','trying','twice','two','un','under','unfortunately','unless','unlikely','until','unto','up','upon','us','use','used','useful','uses','using','usually','value','various','very','via','viz','vs','want','wants','was','way','we','welcome','well','went','were','what','whatever','when','whence','whenever','where','whereafter','whereas','whereby','wherein','whereupon','wherever','whether','which','while','whither','who','whoever','whole','whom','whose','why','will','willing','wish','with','within','without','wonder','would','yes','yet','you','your','yours','yourself','yourselves','zero')