How do I calculate the average time it takes to post comments on a stackexchange using SQL?
I'm looking for a SQL query that calculates the average time for comments (measured per month).
I was able to write a query that measures the average time between the original datetime post and the datetime comment, but still this is not correct, since the time must be measured between the current comment and the previous one as they relate to most times.
select
dateadd(month, datediff(month, 0, Comments.creationdate),0) [Date],
AVG(CAST(DATEDIFF(hour, Posts.CreationDate, Comments.creationdate ) AS BigInt)) [DelayHours]
from comments
INNER JOIN posts ON Comments.PostId = Posts.Id
GROUP BY
dateadd(month, datediff(month, 0, Comments.creationdate),0)
ORDER BY Date
source to share
I think something like this should work. Sorry, I cannot verify this at the moment; I am sorry if I made a typo.
WITH cte1 AS
(
SELECT c.PostId, c.creationdate,
ROW_NUMBER() OVER (PARTITION BY c.PostId ORDER BY c.creationdate) AS rn
FROM comments c
)
SELECT dateadd(month, datediff(month, 0, a.creationdate),0) [Date],
AVG(diff_hr) AS avg_diff
FROM
(
SELECT a1.PostId, a1.creationdate,
CASE
WHEN a1.rn = 1 THEN
CAST(DATEDIFF(hour,p.creationdate,a1.creationdate) AS BIGINT)
ELSE
CAST(DATEDIFF(hour,a2.creationdate,a1.creationdate) AS BIGINT)
END AS diff_hr
FROM cte1 a1
INNER JOIN posts p ON (p.Id = a1.PostId)
LEFT JOIN cte1 a2 ON (a2.PostId = a1.PostId AND a2.rn = a1.rn-1)
)a
GROUP BY dateadd(month, datediff(month, 0, a.creationdate),0)
Update
For SQLServer 2012 the LAG
solution will be simplified ... I noticed the version comment too late.
Update 2 Bug fixes (omitted sentence FROM
and p.PostId
changed to p.Id
to match table definition)
source to share