0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence

declare @Competitions table (
  answer int, 
  question int, 
  tags nvarchar(250),
  body nvarchar(max), 
  score int, 
  winner bit,
  idx float
);

Insert into @Competitions
Select
  answers.Id as answer, 
  answers.ParentId as question,
  questions.Tags as tags,
  answers.Body as body,
  answers.Score as score,
  case when answers.Id = questions.AcceptedAnswerId then 1 else 0 end as winner,
  idx
FROM Posts answers 
JOIN (
  SELECT Id, Score, AcceptedAnswerId, ViewCount, AnswerCount, Tags, 
         RAND(Id+1) as idx,
         concat('https://stackoverflow.com/questions/', Id) as link
  FROM Posts
  WHERE PostTypeId = 1 
    and AnswerCount > 1
    and (Tags like '%<java>%' 
         or Tags like '%<python>%' 
         -- or Tags like '%<r>%'
        ) 
    and Tags not like '%<javascript>%' and Tags not like '%<android>%'
    and year(CreationDate) BETWEEN 2011 AND 2020
    and Score >= 10
) questions 
on (answers.ParentId = questions.Id)
where answers.PostTypeId = 2 
and answers.Body like '%</code></pre>%'
ORDER BY idx;
    
SELECT c.question, tags, count(answer) as cnt,
max(score) as max, avg(score) as average, stdev(score) as std,
case when stdev(score) = 0 then avg(score) * 2 else avg(score) / stdev(score) end as width,
concat('https://stackoverflow.com/questions/', c.question) as link
FROM @Competitions c  
group by c.question, tags
having count(answer) > 1
order by width desc;
  
-- SELECT TOP 1000 
--   concat('https://stackoverflow.com/questions/', c.question) as question, 
--   tags, cnt 
-- FROM (SELECT question, max(idx) as idx, max(answer) as answer, 
--              max(tags) as tags 
--       FROM @Competitions 
--       GROUP BY question) c
-- JOIN (SELECT question, count(*) cnt FROM @Competitions GROUP BY question) ct 
-- ON (c.question = ct.question)
-- WHERE cnt > 1
-- ORDER BY idx;

fork query

edited jan 12

created nov 18 Justin.Middleton