posts with more image markdown than html images

2

Please login or register to vote for this query.

(click on this box to dismiss)

Mi Yodeya

Q&A for those who base their lives on Jewish law and tradition and anyone interested in learning more

select postid as [Post Link],
mdi.numimages as numMarkdownImages,
mdi.numdirectimages as numDirectImages,
hi.numimages as NumHtmlImages,
hi.viewcount as ViewCount,
hi.score as Score,
hi.creationdate as CreationDate
from


(select postid,len(markdown) - len(replace(markdown, '<img src=', '<img src')) 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,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