Upvotes/downvotes on posts by "newish" users - multisite

0

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

Q&A for professional mathematicians

---based on this template: https://data.stackexchange.com/stackoverflow/query/1004129/multi-db-template-with-hostname

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , countupvotes integer
                    , countdownvotes integer
                    , countposts integer
                    )

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
     , ''',hostname ,N'''
     , SUM(Case When (v.VoteTypeId=2) Then 1 Else 0 End)
     , SUM(Case When (v.VoteTypeId=3) Then 1 Else 0 End)
     , COUNT(DISTINCT p.Id)
from ', quotename(name), N'.dbo.posts p left outer join'
      , quotename(name), N'.dbo.Votes v on v.PostId=p.Id inner join'
      , quotename(name), N'.dbo.users u on p.OwnerUserId=u.Id
  where (p.PostTypeId=1) and (DATEDIFF(day, u.CreationDate, p.CreationDate)<=##days?100##)
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall

exec (@sql)


select 
  site, hostname,
  countposts,
  countupvotes, countdownvotes, 
  ROUND(CAST(countupvotes AS decimal)/CAST(countposts AS decimal), 2) AS [Upvotes per post],
  RANK() OVER(ORDER BY ROUND(CAST(countupvotes AS decimal)/CAST(countposts AS decimal), 2) DESC) AS Rank,  
  ROUND(CAST(countdownvotes AS decimal)/CAST(countposts AS decimal), 2) AS [Downvotes per post],
  RANK() OVER(ORDER BY ROUND(CAST(countdownvotes AS decimal)/CAST(countposts AS decimal), 2) DESC) AS Rank  
from #result
ORDER BY ROUND(CAST(countdownvotes AS decimal)/CAST(countposts AS decimal), 2) DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results