0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

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

-- Average lifespan 
SELECT avg(row) FROM (
SELECT ROW_NUMBER() OVER (ORDER BY hours) row, hours
FROM (
SELECT DATEDIFF(hour, CreationDate, LastActivityDate) hours
  FROM Posts
  WHERE (Tags like '%<java>%' 
         or Tags like '%<python>%' 
         or Tags like '%<r>%') 
         and Tags not like '%<javascript>%'
         and year(CreationDate) = 2018
         ) hours_tab) t


-- SELECT *
-- FROM Posts
-- WHERE AnswerCount > 1
--  and (Tags like '%<java>%' 
--       or Tags like '%<python>%' 
--       or Tags like '%<r>%') 
--  and Tags not like '%<javascript>%'

--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
--  FROM Posts answers 
--  JOIN (
--    SELECT TOP 1000000 * FROM Posts WHERE AnswerCount > 3 
--    and (Tags like '%<java>%' or Tags like '%<python>%' or Tags like '%<r>%') 
--    and Tags not like '%<javascript>%' and Tags not like '%<android>%'
--    and PostTypeId = 1
--    order by LastEditDate desc
--  ) questions 
--  on (answers.ParentId = questions.Id)
--  where answers.PostTypeId = 2 
--  and answers.Body like '%</code></pre>%'
--;
  
-- select * from @ExecutableAnswers;
--select e.question, answer, tags, s, score, body
--from (select question, count(*) c, avg(score) s 
--      from @Competitions group by question) tab
--      JOIN @Competitions e on (e.question = tab.question) 
--      order by s desc, e.question, score desc;

view current version

edited nov 18 20

created nov 18 20 Justin.Middleton