1

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

1
  • @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
4

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.

8
  • 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
  • I should probably test this myself, but is sp_executesql available on SEDE? If so, you can use that to execute a dynamic query against the top tags. Jul 27 '20 at 8:23
  • The function is available: meta.stackexchange.com/questions/334898/… Jul 27 '20 at 8:25
  • @MartijnPieters it is but in the introduction I confessed: actually I know how to do it but I'm lazy. That has not changed 6 to 8 weeks later ;)
    – rene
    Jul 27 '20 at 8:47
  • Damn, I misread that! Sorry, here I thought I was being helpful :-D Jul 27 '20 at 8:49
  • @MartijnPieters you're helpful in making me feel more guilty for not revisiting this. I get to it, soon ... ish ....;)
    – rene
    Jul 27 '20 at 8:51
  • 1
    I’ll set a calendar reminder to chide you again in 6-8 weeks then! And if I had a penny for every post I know I could improve if I spent some time on it I’d have more time to spend on improving posts instead of having to do paying work.. Jul 27 '20 at 8:52

You must log in to answer this question.

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