1

Please login or register to vote for this query.

(click on this box to dismiss)

Distribution of the number of edits per answer, excluding edits by the original answer, and excluding community wiki posts

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

-- Number of non-author edits per answer
-- Distribution of the number of edits per answer,
-- excluding edits by the original answer, and excluding community wiki posts

CREATE TABLE #EditCounts (Id int NOT NULL, Edits int NOT NULL)
go
INSERT INTO #EditCounts
    SELECT ph.PostId AS [Id], Count(*) AS [Edits]
    FROM PostHistory ph, Posts p
    WHERE PostHistoryTypeId = 5
    AND p.Id = ph.PostId
    AND p.OwnerUserId != ph.UserId
    GROUP BY ph.PostId
go
WITH TotalAnswers AS (
    SELECT Cast(Count(*) AS FLOAT) AS Count
    FROM Posts
    WHERE PostTypeId = 2
      AND CommunityOwnedDate IS NULL
)
SELECT
    IsNull(e.Edits, 0) AS [Edits],
    Count(*) AS [Answers],
    Round(Count(*) / (SELECT Count FROM TotalAnswers) * 100, 1) As [Percent]
FROM Posts p
LEFT JOIN #EditCounts e ON e.Id = p.Id
WHERE p.PostTypeId = 2
  AND p.CommunityOwnedDate IS NULL
GROUP BY e.Edits
ORDER BY Edits

fork query

created jul 23 11 Gilles