0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Chinese language

-- Tags: Spelling variants (dashes)

with
PostCounts as (
  select
     Tags.Id         as TagId,
     count(Posts.Id) as PostCount
  from Posts    inner join
       PostTags on Posts.Id = PostTags.PostId
                inner join
       Tags     on PostTags.TagId = Tags.Id
  group by Tags.Id
),
Undashed as (
  select
    replace(TagName, '-', '') as vname,
    *
  from tags
),
HasDashes as (
  select *
  from Undashed
  where TagName != vname
)

select
  v1.TagName,
  c1.PostCount,
  case when c1.PostCount > c2.PostCount then '<-' else '->' end,
  v2.TagName,
  c2.PostCount
from
  Undashed v1   inner join
  HasDashes v2  on v1.vname = v2.vname
                inner join
  PostCounts c1 on v1.Id = c1.TagId
                inner join
  PostCounts c2 on v2.Id = c2.TagId
where v1.Id != v2.Id
  and (v1.TagName = v1.vname or -- no dashes in v1
       v1.TagName > v2.TagName) -- otherwise list only one combination
order by
  case when c1.PostCount > c2.PostCount then c1.PostCount else c2.PostCount end
  desc;

fork query

created aug 9 10 sth