## My median and average answer score at given date

3

(click on this box to dismiss)

Reports median and average answer score. The MedianAnswerScore2 is more precise version of median.

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

``````declare @median int; -- clasic median

SELECT @median = MAX(Score)
FROM (SELECT TOP 50 percent Score
FROM Posts as PU
WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL
and PU.OwnerUserId = ##UserId##
and PU.CreationDate <= '##Date##'
ORDER BY Score
) as t;

-- improved median
declare @median2_below50 int;
declare @median2_total int;

select @median2_below50 =
(             SELECT count(*)
FROM (SELECT TOP 50 percent Score
FROM Posts as PU
WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL
and PU.OwnerUserId = ##UserId##
and PU.CreationDate <= '##Date##'
ORDER BY Score
) as t
WHERE Score = @median
)

select @median2_total =
(SELECT count(*)
FROM Posts as PU
WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL
and PU.OwnerUserId = ##UserId##
and Score = @median
and PU.CreationDate <= '##Date##'
)

-- report results
SELECT
@median + cast(@median2_below50 as DECIMAL(3,0)) / @median2_total AS MedianAnswerScore2,
avg(cast(Score as DECIMAL(3,0))) as AverageScore
FROM
Posts
WHERE
PostTypeId = 2 and CommunityOwnedDate IS NULL and OwnerUserId = ##UserId##
and CreationDate <= '##Date##'

--  select @median2_below50, @median2_total ;``````

### Enter Parameters

Options:
-
Switch to main site
Hold tight while we fetch your results
:records returned in :time ms:cached