0

I'd like to write a query in the Stack Exchange Data Explorer for the Cross Validated site, which returns me the Question and Answers on Cross Validated by month and by tag, for the 10 tags which today have the most unanswered questions.

Thus, similar to this query, but, for each of the 10 most popular tags, I'd like to see the time trend of total questions and unanswered questions (instead than total questions and answered questions).

In other words, the query should return a table with 21 columns: a date column date, and two count columns for each tag.

Problem is, I don't know SQL. Can you help me?

(if this isn't possible, it's ok to select the 10 tags which have the most questions, i.e., most popular tags).

  • @rene thanks! I need to see how the fraction of unanswered questions has grown with time - so I either need total questions and unanswered questions, or total questions and answered questions (then I can compute the unanswered ones as difference). – DeltaIV Jul 11 '18 at 19:47
2

I have created the following query that can select the top 10 tags that don't have answers today.

It uses a PIVOT query.

Unfortunately I can't / haven't found an easy/nice way to make the columns dynamic based (actually I know how to do it but I'm lazy) on the first tags query. So instead I have a second helper query that produces the needed columns for me that I can then copy/paste in the actual query. I have indicated where the copy paste needs to take place.

To have two series for the same tag (total and unanswered sum) I have used a union to produces a source query that has all the data in it. SQL Server does the grouping, creates the columns based and sums the results.

Here is the source:

-- 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
)

/*
-- for debug
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

SEDE has an awesome tutorial which is definitely worth following, even if you have no SQL experience whatsoever.

The data in SEDE is updated once a week, on Sunday morning.

  • when I run the query some cells are empty. Is that correct? Does it mean no questions with that tag were asked that month (or no questions were left unanswered)? – DeltaIV Jul 11 '18 at 21:44
  • @DeltaIV yeah, those are NULL values. No rows were present in that month. – rene Jul 12 '18 at 6:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .