revision comments with links

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- result set 1: links in unique revisions for types 4,5,6
-- result set 2: links by posthistorytype, all types

select
  l.*,
  format(1.0 * links / revisions, '0.00%') pctlinks
from (
  select
    count(iif(comment like '##Pattern?%://%##', revisionguid, null)) links,
    count(*) revisions
  from (
    select distinct 
      revisionguid,
      comment 
    from 
      posthistory 
    where
      posthistorytypeid in (4,5,6)
    ) h
  ) l

select
  l.*,
  format(1.0 * links / total, '0.00%') pctlinks,
  t.name
from (
  select
    posthistorytypeid,
    count(*) links,
    (select count(*) from posthistory where posthistorytypeid = h.posthistorytypeid) total
  from
    posthistory h
  where
    comment like '##Pattern##'
  group by
    posthistorytypeid) l
  inner join posthistorytypes t on t.id = l.posthistorytypeid
order by
  l.posthistorytypeid

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results