My favorite SE site gets a lot of bad questions, and a large amount of them get closed, it got me wondering if there were other SE sites that get as many bad questions, or if my chosen favorite just attracts more than it's fair share of stinkers. Are there any analytics for keeping track of how many questions each SE site closes and deletes?
-- result table, don't rename and keep the site column create table #results ( site nvarchar(250) , qt int , q int , qct int , dq int , cq int , dcq int); declare @sql nvarchar(max) = '' -- holds build up sql string -- build one biq union sql, for each db select @sql = @sql + iif( len(@sql) > 1 , 'union' , 'insert into #results' ) + -- here goes the per site query, fully qualify the database objects N' select ''' + name + ''' , count(*) qt , sum(case when deletiondate is null then 1 else 0 end) q , sum(case when closeddate is not null then 1 else 0 end) qct , sum(case when deletiondate is not null then 1 else 0 end) dq , sum(case when deletiondate is null and closeddate is not null then 1 else 0 end) cq , sum(case when deletiondate is not null and closeddate is not null then 1 else 0 end) dcq from ' + quotename(name) + '.dbo.postswithdeleted where posttypeid =1 ' from sys.databases where database_id > 5 -- and (name not like '%.Meta' or name = 'StackExchange.Meta') --print @sql -- execute it exec (@sql) -- show results select site -- rest of columns , qt as [al questions] , q as [visible questions] , qct as [all closed questons] , dq as [deleted questions] , cast(qct as decimal) / qt as [c/q ratio] , cq as [closed questions] , dcq as [delete closed questions] from #results order by cast(qct as decimal) / qt desc drop table #results
As I don't think absolute numbers will be interesting I ordered the result by the closed/questions ratio (overall, so both deleted and visible posts).
When run today this will be the result:
And Programmers (now called Software Engineering) is the winner(?)!
Keep in mind SEDE is updated only once a week, on Sunday.