WITH TagCounts AS ( SELECT Tags.TagName, COUNT(*) AS TagC...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Islam Meta

Q&A about the site for Muslims, experts in Islam, and those interested in learning more about Islam

WITH TagCounts AS (
    SELECT
        Tags.TagName,
        COUNT(*) AS TagCount,
        SUM(Posts.ViewCount) AS TotalViewCount,
        YEAR(Posts.CreationDate) AS CreationYear,
        MONTH(Posts.CreationDate) AS CreationMonth
    FROM
        Tags
    JOIN
        PostTags ON Tags.Id = PostTags.TagId
    JOIN
        Posts ON PostTags.PostId = Posts.Id
    WHERE
        Posts.PostTypeId = 1
        AND Posts.CreationDate >= DATEADD(year, -1, GETDATE())
    GROUP BY
        Tags.TagName,
        YEAR(Posts.CreationDate),
        MONTH(Posts.CreationDate)
)

SELECT
    TC1.TagName,
    TC1.CreationYear,
    TC1.CreationMonth,
    TC1.TagCount,
    TC1.TotalViewCount,
    TC1.TagCount - ISNULL(TC2.TagCount, 0) AS MonthOverMonthChange
FROM
    TagCounts TC1
LEFT JOIN
    TagCounts TC2 ON TC1.TagName = TC2.TagName
    AND TC1.CreationYear = TC2.CreationYear
    AND TC1.CreationMonth = TC2.CreationMonth + 1
ORDER BY
    TC1.CreationYear DESC,
    TC1.CreationMonth DESC,
    TC1.TagCount DESC;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results