Unanswered questions over time in top tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla

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

;with TagsNoAnswers as 
(
  select top 10  
        tagname
      , count(*) [q-cnt]
  from postswithdeleted 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 sites:
loading Hold tight while we fetch your results