0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for enthusiasts and power users of the Android operating system

Declare @startid int = ##Startid##;
Declare @stopid int = ##Stopid##;

With aqpcount As (
Select u.Id, u.DisplayName, u.CreationDate, u.WebsiteUrl, u.Location, u.Reputation, AnswerCount, QuestionCount, AllPostCount
From Users As u
Left Join
(
Select p.OwnerUserId,
Sum(case when p.PostTypeId = 1 then 1 else 0 end) As QuestionCount,
Sum(case when p.PostTypeId = 2 then 1 else 0 end) As AnswerCount,
Sum(case when p.PostTypeId >= 1 then 1 else 0 end) As AllPostCount
From Posts As p
Group by p.OwnerUserId
) As aq
On u.Id = aq.OwnerUserId
), firstpost As (
Select x.id, x.DisplayName, x.firstPostDate, p.PostTypeId as firstPostTypeId, p.Score as firstPostScore
From Posts p Join (
Select u.id, u.DisplayName, MIN(p.CreationDate) as firstPostDate
From Users As u
Left Join Posts As p On
p.OwnerUserId = u.Id
Where p.PostTypeId <= 2
Group By u.id,u.DisplayName
) As x On p.OwnerUserId = x.Id AND p.CreationDate = x.firstPostDate
)
--Select a.Id, a.DisplayName, a.Location, a.CreationDate,
-- AnswerCount, QuestionCount,
Select a.*,
1.0*AnswerCount/nullif(QuestionCount,0) As AnswersPerQuestion,
fp.firstPostDate, fp.firstPostTypeId, fp.firstPostScore
--fp.firstPostDate, fp.PostTypeId
From aqpcount as a
inner join firstpost as fp on a.Id = fp.id
--left join firstpost as fp on a.Id = fp.id
--Where AnswerCount > QuestionCount
Where a.Id >= @startid and a.Id < @stopid
order by a.Id asc

fork query

edited nov 12 18

created nov 12 18 anonymous