0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

WITH TempTable AS (
    SELECT 
        OwnerUserId,
        COUNT(CASE WHEN CreationDate BETWEEN '2022-09-04' AND '2022-11-27' THEN Id END) AS CountPeriodA,
        COUNT(CASE WHEN CreationDate BETWEEN '2022-12-04' AND '2023-02-26' THEN Id END) AS CountPeriodB
    FROM posts
    WHERE PostTypeId = 1
    GROUP BY OwnerUserId
    HAVING 
        COUNT(CASE WHEN CreationDate BETWEEN '2022-09-04' AND '2022-11-27' THEN Id END) >= 1
        AND 
        COUNT(CASE WHEN CreationDate BETWEEN '2022-12-04' AND '2023-02-26' THEN Id 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 '2022-09-04' AND '2023-02-26'
), 
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 7

created dec 7 jon.doe45585