0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

WITH LockedPosts AS (
    SELECT
        p.Id AS PostId,
        p.CreationDate AS PostCreationDate,
        lh.CreationDate AS LockDate,
        p.AnswerCount,
        DATEDIFF(day, p.CreationDate, lh.CreationDate) AS DaysBetween,
        (SELECT MIN(CreationDate) 
         FROM Posts a 
         WHERE a.ParentId = p.Id AND a.PostTypeId = 2) AS FirstAnswerDate,
        (SELECT MAX(CreationDate) 
         FROM Posts a 
         WHERE a.ParentId = p.Id AND a.PostTypeId = 2) AS LastAnswerDate
    FROM
        Posts p
    JOIN
        PostHistory lh ON p.Id = lh.PostId
    WHERE
        lh.PostHistoryTypeId = 14 -- 14 is the PostHistoryTypeId for "Lock"
        AND p.PostTypeId = 1 -- Ensuring we are only selecting questions (PostTypeId = 1)
        AND lh.CreationDate < '2020-01-01'
),
AnswerTimeDiff AS (
    SELECT
        PostId,
        DaysBetween,
        AnswerCount,
        DATEDIFF(day, FirstAnswerDate, LastAnswerDate) AS AnswerTimeSpan
    FROM 
        LockedPosts
    WHERE
        FirstAnswerDate IS NOT NULL 
        AND LastAnswerDate IS NOT NULL
)

SELECT 
    ROUND(AVG(DaysBetween), 2) AS AvgDaysBetween,
    ROUND(STDEV(DaysBetween), 2) AS StdevDaysBetween,
    ROUND(AVG(AnswerCount), 2) AS AvgAnswerCount,
    ROUND(STDEV(AnswerCount), 2) AS StdevAnswerCount,
    ROUND(AVG(AnswerTimeSpan), 2) AS AvgAnswerTimeSpan,
    ROUND(STDEV(AnswerTimeSpan), 2) AS StdevAnswerTimeSpan
FROM 
    AnswerTimeDiff;

fork query

edited sep 9

created sep 9 anonymous