8
\$\begingroup\$

I was inspired to write a query that produced a graph mapping the perceived quality of your Stack Exchange answers over time.... in other words, "Are your answers getting better, or worse?"

Because answer scores are very 'spiky', it seems natural to calculate a sliding average with a configurable window, to show trends.

The result I have is:

enter image description here

And this is the SEDE query.

with Rolling as (
    Select Rank() over (order by CreationDate Asc) as Answer,
           CreationDate,
           Convert(float, Score) as Score
    from Posts
    where PostTypeId = 2
      and OwnerUserId = ##UserId:int##
)
select Master.CreationDate, Master.Score as [Score], Avg(Slave.Score) as [RollingAvg]
from Rolling as Master,
     Rolling as Slave
where Slave.Answer  > Master.Answer - ##RollingAvg:int?10##
  and Slave.Answer <= Master.Answer
group by Master.CreationDate, Master.Score
order by Master.CreationDate

I am particularly interested to know whether the rolling average can be computed in a more SQL Server friendly way. There does not appear to be a performance problem, but the CTE and self join seem.... ugly.

Any other observations?

\$\endgroup\$
  • \$\begingroup\$ Other observations? Yes, apparently my answers suck recently... \$\endgroup\$ – Simon Forsberg Sep 4 '14 at 22:28
  • 2
    \$\begingroup\$ Another general observation: In some of your queries you are using UserId as input, in others you require the username as input. Consistency, please! \$\endgroup\$ – Simon Forsberg Sep 4 '14 at 22:29
5
\$\begingroup\$

There is a simple way to calculate rolling averages in SQL, with a caveat.

SELECT CreationDate
     , Score
     , avg(CAST(Score AS FLOAT)) OVER (
         ORDER BY CreationDate
         ROWS BETWEEN ##RollingAvg:INT?10## PRECEDING AND CURRENT ROW
       ) AS RollingAvg
    FROM Posts
    WHERE PostTypeId = 2 AND OwnerUserId = ##UserId:INT##;

The problem with that is that a window that includes up to n preceding rows is one row larger than the window in your original query. Unfortunately, SQL Server requires the window frame bound to be specified using an unsigned integer literal — it cannot be an expression.

That turns out to be the most annoying off-by-one error to fix:

DECLARE @UserId AS INT = ##UserId##;
DECLARE @RollingAvg AS INT = ##RollingAvg:INT?10##;

DECLARE @Sql AS NVARCHAR(1000) = 'SELECT CreationDate
     , Score
     , avg(CAST(Score AS FLOAT)) OVER (
         ORDER BY CreationDate
         ROWS BETWEEN ' + CAST(@RollingAvg - 1 AS VARCHAR) + ' PRECEDING AND CURRENT ROW
       ) AS RollingAvg
    FROM Posts
    WHERE PostTypeId = 2 AND OwnerUserId = ' + CAST(@UserId AS VARCHAR);

EXEC sp_executesql @Sql;

It's arguable that your original formulation, calculating the window average the "hard" way, is better than constructing the SQL dynamically to be EXECuted.

Of course, if you are willing to redefine the meaning of the window size parameter, then the window aggregate function would be the way to go.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I have edited my SEDE query on SEDE, and incorporated your changes. I resolved the issue with the off-by-one by renaming my parameter..... to make it more obvious. Note, I also got rid of the BETWEEN span, and replaced it with just the 'PRECEDING' which includes the Current row by default. \$\endgroup\$ – rolfl Sep 9 '14 at 18:12
4
\$\begingroup\$

There is a pretty big flaw with your logic. This query might work okay here, but wouldn't work as well on a site like Stack Overflow where it's more likely for an answer to get upvoted long after it was originally posted. The problem is that the query will be naturally skewed toward older answers simply because they've had longer to accrue votes. Unfortunately, I don't know quite enough about the schema to offer an alternative approach.


This line could use a comment.

where PostTypeId = 2 --Answers

I like that you didn't bother with the extra join, but if you're going to use integer key values in your Where statement, you should clarify what the value represents. Otherwise, the person looking at it will have to figure out which table the key is stored in and look it up to figure out what it represents.

| improve this answer | |
\$\endgroup\$
  • 2
    \$\begingroup\$ Very often I find that the number of votes on an answer is more about exposure than quality. I think that 'problem' is much bigger than this query. \$\endgroup\$ – Simon Forsberg Sep 4 '14 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.