0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people seeking specific hardware recommendations

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

view current version

edited dec 14

created dec 14 jon.doe45585