0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

WITH TempTable AS (
    SELECT 
        OwnerUserId,
        COUNT(CASE WHEN CreationDate BETWEEN '2021-09-05' AND '2021-11-28' THEN Id ELSE NULL END) AS CountPeriodA,
        COUNT(CASE WHEN CreationDate BETWEEN '2021-12-05' AND '2022-02-27' THEN Id ELSE NULL END) AS CountPeriodB
    FROM posts
    WHERE PostTypeId = 1
    GROUP BY OwnerUserId
    HAVING 
        COUNT(CASE WHEN CreationDate BETWEEN '2021-09-05' AND '2021-11-28' THEN Id ELSE NULL END) >= 1
        AND 
        COUNT(CASE WHEN CreationDate BETWEEN '2021-12-05' AND '2022-02-27' 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 '2021-09-05' AND '2022-02-27'
), 
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  * 
FROM FINAL
where IndexColumn > 100000
order by IndexColumn

view current version

edited dec 7

created dec 7 jon.doe45585