0

Please login or register to vote for this query.

(click on this box to dismiss)

I have a website, please come on visit my website

Q&A about the site for Joomla! administrators, users, developers and designers

-- Seek and Destroy: Auto Promoter
-- I have a website, please come on visit my website

select top 5000  *, cast((100 * [nbQuote])/[Total Posts] as varchar) + '%' as [Ratio]
from
(
    select 
       u.id as [User Link],         -- get users
       u.reputation as [User Rep],
       u.websiteurl as WebsiteUrl, 
       count(1) as [Total Posts], 
       sum(
           case when upper(body) like '%' + upper(u.websiteurl) + '%' 
                then 1 
                else 0 
           end) as [nbQuote]
   from users u 
     inner join posts p
       on u.id = p.owneruserid
   where len(u.websiteurl) > 0     -- who have a website
   and u.reputation < 10000        -- skip moderators, they can't ban themself
   and u.id != -1                  -- skip Community
   group by u.id, u.reputation, u.websiteurl
) as result
where nbQuote > 1
and (100 * [nbQuote])/[Total Posts] > 20 -- 20%+ of his posts are self promotion
order by (100 * [nbQuote])/[Total Posts] + [nbQuote] * 2 desc

view current version

edited dec 12 19 anonymous

created jul 27 11 Scorpi0