Unanswered questions over time in top tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

--REF: https://meta.stackexchange.com/a/312499/156316

;with TagsNoAnswers as 
(
  select top 10  
        tagname
      , count(*) [q-cnt]
  from posts q 
  inner join posttags pt on pt.postid = q.id
  inner join tags t on t.id = pt.tagid
  where q.posttypeid = 1 -- Questions
  and q.answercount = 0
  group by tagname
  order by count(*) desc
)

/*
select *
from tagsnoanswers
*/

/*
Results as at 2020-07-27
javascript
278654
java
236898
python
230585
android
224832
c#
181143
php
172676
html
112569
jquery
105367
ios
99325
c++
78953
*/

SELECT  [month],      

-- copy paste columns here     
[javascript],[javascript unanswered],
[java],[java unanswered],
[python],[python unanswered],
[android],[android unanswered],
[c#],[c# unanswered],
[php],[PHP unanswered],
[html],[html unanswered],
[jquery],[jQuery unanswered],
[ios],[iOS unanswered],
[c++],[C++ unanswered]

  -- end copy paste
FROM     
(select eomonth(p.creationdate) [month], t.tagname, count(*) [q-cnt]    
 from posts p inner join posttags pt on pt.postid = p.id     
 inner join tags t on t.id = pt.tagid     
 inner join tagsnoanswers tn on tn.tagname = t.tagname     
 where creationdate <= eomonth(dateadd(m,-1,(select max(creationdate) from posts)))   
 group by eomonth(p.creationdate), t.tagname     
  union      
 select eomonth(p.creationdate) [month]          , concat(t.tagname, ' ', 'unanswered')          , count(*) [q-cnt]     
from posts p     
inner join posttags pt on pt.postid = p.id     
inner join tags t on t.id = pt.tagid     
inner join tagsnoanswers tn on tn.tagname = t.tagname    
where creationdate <= eomonth(dateadd(m,-1,(select max(creationdate) from posts))) and p.answercount = 0     
group by eomonth(p.creationdate), t.tagname )  AS src
PIVOT  (sum([q-cnt])  FOR   tagname    IN (       
-- copy paste columns here (remove first , ) 

[javascript],[javascript unanswered],
[java],[java unanswered],
[python],[python unanswered],
[android],[android unanswered],
[c#],[c# unanswered],
[php],[PHP unanswered],
[html],[html unanswered],
[jquery],[jQuery unanswered],
[ios],[iOS unanswered],
[c++],[C++ unanswered]

 -- end copy-paste     
)  ) AS pvt  order by month
         
         -- this query generates the columns you need to copy paste in the actual query
         
select top 10  concat(', [', tagname, '], [', tagname, ' unanswered]'), count(*) [q-cnt]  from posts q   
inner join posttags pt on pt.postid = q.id  
inner join tags t on t.id = pt.tagid  where q.posttypeid = 1 
and q.answercount = 0  group by tagname  order by count(*) desc

Enter Parameters

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