Roomba-eligible questions sans comment - possible impact to low traffic tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @query_date DATE = GETDATE() - 90;

WITH QuestionStatistics AS (
    SELECT
      t.TagName,
      AVG(CAST(p.ViewCount AS BIGINT))                AS AverageViewCount,
      AVG(DATEDIFF(DAY, p.CreationDate, @query_date)) AS AverageDaysOld,
      COUNT(*)                                        AS TotalQuestions
    FROM PostsWithDeleted p
      LEFT JOIN PostTags pt ON pt.PostId = p.Id
      LEFT JOIN Tags t ON pt.TagId = t.Id
    WHERE p.PostTypeId = 1
          AND p.Score = CASE WHEN p.OwnerUserId IS NULL
      THEN 1
                        ELSE 0 END
          AND p.CreationDate < DATEADD(DAY, -365, @query_date)
          AND p.AnswerCount = 0
          AND p.ViewCount <= DATEDIFF(DAY, p.CreationDate, @query_date) * 1.5
          AND p.DeletionDate IS NULL
          AND COALESCE((SELECT TOP 1 ph.PostHistoryTypeId
                        FROM PostHistory ph
                          INNER JOIN PostHistoryTypes pht ON ph.PostHistoryTypeId = pht.Id
                        WHERE ph.PostId = p.Id
                              AND pht.Name IN ('Post Locked', 'Post Unlocked')
                        ORDER BY ph.Id DESC), 0) <> 14
    GROUP BY t.tagName
)

SELECT *
FROM QuestionStatistics
ORDER BY TotalQuestions ASC, AverageViewCount ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results