posts with more image markdown than html images

0

Please login or register to vote for this query.

(click on this box to dismiss)

Super User

Q&A for computer enthusiasts and power users

select ('https://superuser.com/q/' + cast(postid as nvarchar(max))) as URL,
postid as [Post Link],
mdi.numimages as numMarkdownImages,
mdi.numdirectimages as numDirectImages,
hi.numimages as NumHtmlImages,
hi.viewcount as ViewCount,
hi.score as Score,
CreationDate
from


(select postid,len(markdown) - len(replace(markdown, '<img src="http://', '<img src="http:/')) as numdirectimages,
len(markdown) - len(replace(markdown, '![', 'X')) as numimages
from
(select ph.text as markdown,ph.postid as postid from
posthistory as ph
join 
(select max(id) as id from posthistory where
(posthistorytypeid in (2, 5, 8)) 
group by postid) as latestid
on latestid.id = ph.id) as md) as mdi

join

(
select id,creationdate,score,viewcount,title,len(body) - len(replace(body, '<img src', '<img sr')) as numimages
from posts
) as hi
on mdi.postid = hi.id

where (mdi.numimages > 0 and mdi.numimages > hi.numimages) or
mdi.numdirectimages > 0

Enter Parameters

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