0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people organizing, marketing or licensing open source development projects

DECLARE @start datetime, @end datetime;
DECLARE @Year int, @Month int;
DECLARE @isSetTopic nvarchar(10);
DECLARE @Topic nvarchar(max);
DECLARE @SiteUrl VARCHAR(MAX)

SET @SiteUrl = (
  SELECT
    'https://' +
    CASE WHEN DB_NAME() = 'StackOverflow' THEN 'stackoverflow'
         WHEN DB_NAME() = 'ServerFault' THEN 'serverfault'
         WHEN DB_NAME() = 'SuperUser' THEN 'superuser'
         WHEN DB_NAME() LIKE 'StackExchange.%%' THEN REPLACE(DB_NAME(), 'StackExchange.', '')
         ELSE 'stackexchange' -- Default or if it doesn't match anything specific
    END +
    '.stackexchange.com/q/'
)


SET @Year = ##Year:int##; -- replace 'Year' with the specific year
SET @Month = ##Month:int##; -- replace 'Month' with the specific month
SET @isSetTopic = ##isSetTopic:string##;  -- replace 'isSetTopic' with the specific isSetTopic
SET @Topic = ##interestedTopic:string##; -- replace 'Topic' with the specific topic

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.Body AS QuestionBody,
        p.Id AS QuestionId,
        p.Title AS QuestionTitle,
        p.ViewCount AS ViewCount,
        p.AnswerCount AS AnswerCount,
        p.Score AS QuestionScore,
        CONCAT(LOWER(@SiteUrl), p.Id) AS QuestionURL
    FROM
        Posts p
    WHERE 
        p.PostTypeId = 1 -- This denotes a Question
        AND p.CreationDate >= @start
        AND p.CreationDate < @end
        AND (
            CASE 
                WHEN @isSetTopic = 'false' OR @Topic='' THEN 1
                ELSE 
                    CASE WHEN @isSetTopic = 'true' and (LOWER(p.Body) LIKE '%' + @Topic + '%' OR LOWER(p.Title) LIKE '%' + @Topic + '%') THEN 1
                         ELSE 0 
                    END
            END = 1
        )
),

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 feb 23

created feb 23 anonymous