0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and researchers interested in open data

WITH TempTable AS (
    SELECT 
        OwnerUserId,
        COUNT(CASE WHEN CreationDate BETWEEN '2019-09-01' AND '2019-11-24' THEN Id ELSE NULL END) AS CountPeriodA,
        COUNT(CASE WHEN CreationDate BETWEEN '2019-12-01' AND '2020-02-23' THEN Id ELSE NULL END) AS CountPeriodB
    FROM posts
    WHERE PostTypeId = 1
    GROUP BY OwnerUserId
    HAVING 
        COUNT(CASE WHEN CreationDate BETWEEN '2019-09-01' AND '2019-11-24' THEN Id ELSE NULL END) >= 1
        AND 
        COUNT(CASE WHEN CreationDate BETWEEN '2019-12-01' AND '2020-02-23' THEN Id ELSE NULL END) >= 1
),
UserQuestions AS (
    SELECT 
        p.OwnerUserId,
        p.Id AS QuestionId,
        p.CreationDate AS QuestionCreationDate
    FROM posts p
    INNER JOIN TempTable tt ON p.OwnerUserId = tt.OwnerUserId
    WHERE p.PostTypeId = 1
),
FirstAnswer AS (
    SELECT 
        a.ParentId AS QuestionId,
        a.CreationDate AS FirstAnswerCreationDate,
        ROW_NUMBER() OVER (PARTITION BY a.ParentId ORDER BY a.CreationDate) AS AnswerRank
    FROM posts a
    WHERE a.PostTypeId = 2
    AND a.CreationDate BETWEEN '2019-09-01' AND '2020-02-23'
), 
FINAL AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY uq.OwnerUserId DESC) AS IndexColumn,
        uq.OwnerUserId,
        uq.QuestionId,
        uq.QuestionCreationDate,
        fa.FirstAnswerCreationDate
    FROM UserQuestions uq
    LEFT JOIN FirstAnswer fa ON uq.QuestionId = fa.QuestionId
    WHERE fa.AnswerRank = 1
)
SELECT top 5 * 
FROM FINAL
order by IndexColumn desc

view current version

edited dec 14

created dec 14 jon.doe45585