1

Please login or register to vote for this query.

(click on this box to dismiss)

Find the tags with the best ratio of median-points-per-answer to median-time-between-question.

Q&A for programmers

-- Most Lucrative Tags
-- Find the tags with the best ratio of median-points-per-answer to median-time-between-question.

DECLARE @MaxDate datetime
SELECT
    @MaxDate = MAX([CreationDate])
FROM
    [Posts] q
WHERE
    q.[PostTypeId] = 1

SELECT
    ROW_NUMBER() OVER (ORDER BY q.[CreationDate], q.[Id]) [Id],
    q.[CreationDate],
    q.[Id] [QuestionId]
INTO
    #Questions
FROM
    [Posts] q
WHERE
    q.[PostTypeId] = 1

SELECT
    q.[Id],
    q.[CreationDate],
    t.[TagId]
INTO
    #TagDates
FROM
    #Questions q
INNER JOIN
    [PostTags] t
  ON
    q.[QuestionId] = t.[PostId]

SELECT
    [TagId],
    ISNULL((SELECT MAX([CreationDate]) FROM #TagDates t2 WHERE t2.[Id] > t1.[Id] AND t2.[TagId] = t1.[TagId]), @MaxDate) - [CreationDate] [Diff],
    [Id]
INTO
    #TimeToNextTag
FROM
    #TagDates t1

SELECT ttnt.[TagId], t.[TagName], AVG(CAST([Diff] as float)) [MTBQ], COUNT(*) [Questions] FROM #TimeToNextTag ttnt INNER JOIN [Tags] t ON ttnt.[TagId] = t.[Id]  GROUP BY ttnt.[TagId], t.[TagName] HAVING COUNT(*) > 1 ORDER BY ttnt.[TagId]

fork query

created feb 19 11 otac0n