patternsqlMinor

Finding the use of the word 'Cthulhu' in tags on Stack Overflow

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

thestackoverflowcthulhutagswordfindinguse

Problem

I've written the following SQL to count the number of times the name 'Cthulhu' turns up for each tag on Stack Overflow (original here):

select t.TagName, count (*) 'Tainted'
from Posts p, Tags t, PostTags pt
where p.Body like '%cthulhu%'
and (pt.PostId = p.Id and t.Id = pt.TagId)
group by t.TagName
order by Tainted DESC, t.TagName ASC

It works, but I'm not used to cutting SQL manually; I'm more accustomed to using ORMs. I tried using CONTAINS instead of LIKE, but apparently Body isn't set up for full-text search.

Could you please provide me some feedback - in particular, are there any best practices I'm missing, and whether there are standard formatting rules for SQL that would make it a bit easier on the eye?

Solution

select t.TagName, count (*) 'Tainted'
  from Posts p
 inner join PostTags pt on (pt.PostId == p.Id)
 inner join Tags t on (t.Id == pt.TagId)
 where lower(p.Body) like '%cthulhu%'
 group by t.TagName
 order by Tainted desc, t.TagName asc

Notice the lower on the body, because like is (should?) be case sensitive.

The joins are also easier to read IMHO than the conditions in the where clause.

The formatting is based on right-aligning keywords and left-aligning clauses.

Code Snippets

select t.TagName, count (*) 'Tainted'
  from Posts p
 inner join PostTags pt on (pt.PostId == p.Id)
 inner join Tags t on (t.Id == pt.TagId)
 where lower(p.Body) like '%cthulhu%'
 group by t.TagName
 order by Tainted desc, t.TagName asc

Context

StackExchange Code Review Q#3158, answer score: 2

Revisions (0)

No revisions yet.