0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

--find top 10 users with highest reputation whose name starts with v

--find all questions in dec 2020 where there was a answer with higher
--score than the accepted ans

--columns: Question title, date,accepted answer score,highest answer score
--accepted answer score>=10

with questionindec2020 as(
select p.Id as QuestionId,
cast(p.creationdate as date) as creationdate
from posts p
inner join posttypes pt
on p.posttypeid = pt.id 
where pt.Name = 'Question'
and (creationdate between '2020-12-01' and '2020-12-31')),

lowscoredacceptedanswers as(
select q.Id as QuestionId,max(p.score) as HighestAnswerScore ,
max(a.score) as AcceptedAnswerScore
from posts q
inner join posts a
on q.AcceptedAnswerId = a.Id
inner join posts p
on p.ParentId = q.Id 
where p.score>a.score 
and a.score>=10
group by q.Id)

select top 20 
q.QuestionId, q.QuestionId as [Post Link],q.creationdate, 
l.HighestAnswerScore, l.AcceptedAnswerScore
from questionindec2020 q inner join lowscoredacceptedanswers l
on q.QuestionId = l.QuestionId

fork query

edited jan 14

created jan 14 kamatvedita99