Can I find the number of total questions and unanswered questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/312498/how-can-i-find-the-number-of-total-questions-and-unanswered-questions-for-each/312499#312499

Statistical Analysis

Q&A for people interested in statistics, machine learning, data analysis, data mining, and data visualization

-- which tags habe question with 0 answers
;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
*/

SELECT  [month]
       -- copy paste columns here
      , [r], [r unanswered]                                   
      , [regression], [regression unanswered]                 
      , [machine-learning], [machine-learning unanswered]     
      , [time-series], [time-series unanswered]               
      , [hypothesis-testing], [hypothesis-testing unanswered] 
      , [probability], [probability unanswered]               
      , [distributions], [distributions unanswered]           
      , [logistic], [logistic unanswered]                     
      , [self-study], [self-study unanswered]                 
      , [classification], [classification 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 , )
          [r], [r unanswered]                                   
        , [regression], [regression unanswered]                 
        , [machine-learning], [machine-learning unanswered]     
        , [time-series], [time-series unanswered]               
        , [hypothesis-testing], [hypothesis-testing unanswered] 
        , [probability], [probability unanswered]               
        , [distributions], [distributions unanswered]           
        , [logistic], [logistic unanswered]                     
        , [self-study], [self-study unanswered]                 
        , [classification], [classification 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 -- Questions
  and q.answercount = 0
  group by tagname
  order by count(*) desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results