1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

DECLARE @start datetime, @end datetime;
DECLARE @Year int, @Month int;

SET @Year = ##Year:int##; -- replace 'Year' with the specific year
SET @Month = ##Month:int##; -- replace 'Month' with the specific month

SET @start = CAST(@Year AS varchar(4)) + '-' + RIGHT('00' + CAST(@Month AS varchar(2)), 2) + '-01';

-- SET @start = '2023-11-01'; -- replace 'YYYY-MM' with the specific year and month

SET @end = DATEADD(MONTH, 1, @start);


WITH QuestionData AS (
    SELECT 
        p.Id AS QuestionId,
        p.Title AS QuestionTitle,
        p.ViewCount AS ViewCount,
        p.AnswerCount AS AnswerCount,
        p.Score AS QuestionScore,
        CONCAT('https://dsp.stackexchange.com/q/', p.Id) AS QuestionURL
    FROM
        Posts p
    WHERE 
        p.PostTypeId = 1 -- This denotes a Question
        AND p.CreationDate >= @start
        AND p.CreationDate < @end
),
AnswerData AS (
    SELECT 
        p.ParentId AS QuestionId,
        SUM(p.Score) AS TotalAnswerScore
    FROM 
        Posts p
    WHERE 
        p.PostTypeId = 2 -- This denotes an Answer
    GROUP BY 
        p.ParentId
),
QuestionComments AS (
    SELECT 
        c.PostId AS QuestionId,
        COUNT(*) AS QuestionCommentsCount
    FROM
        Comments c
    WHERE
        c.PostId IN (SELECT QuestionId FROM QuestionData)
    GROUP BY
        c.PostId
),
AnswerComments AS (
    SELECT 
        p.ParentId AS QuestionId,
        COUNT(*) AS AnswerCommentsCount
    FROM
        Comments c
    JOIN
        Posts p ON p.Id = c.PostId
    WHERE
        p.ParentId IN (SELECT QuestionId FROM QuestionData) AND p.PostTypeId = 2
    GROUP BY
        p.ParentId
)
SELECT TOP 10
    qd.QuestionId,
    qd.QuestionTitle,
    qd.QuestionURL,
    qd.ViewCount,
    qd.AnswerCount,
    ISNULL(qd.QuestionScore, 0) + ISNULL(ad.TotalAnswerScore, 0) AS TotalScore,
    ISNULL(qc.QuestionCommentsCount, 0) + ISNULL(ac.AnswerCommentsCount, 0) AS TotalCommentsCount
FROM 
    QuestionData qd
LEFT JOIN 
    AnswerData ad ON qd.QuestionId = ad.QuestionId
LEFT JOIN
    QuestionComments qc ON qd.QuestionId = qc.QuestionId
LEFT JOIN
    AnswerComments ac ON qd.QuestionId = ac.QuestionId
ORDER BY 
    qd.ViewCount DESC,
    qd.AnswerCount DESC,
    TotalScore DESC,
    TotalCommentsCount DESC

fork query

edited nov 29

created nov 29 jon.doe43279