postgresql-count

0

Please login or register to vote for this query.

(click on this box to dismiss)

Game Development Meta

Q&A about the site for professional and independent game developers

-- This query aggregates the total views per month for tags matching certain patterns, case-insensitively
WITH TagViews AS (
    SELECT
        pt.TagId,
        p.CreationDate,
        p.ViewCount
    FROM
        Posts p
    JOIN
        PostTags pt ON p.Id = pt.PostId
    JOIN
        Tags t ON pt.TagId = t.Id
    WHERE
        --LOWER(t.TagName) = 'postgressql' 
        LOWER(t.TagName) = 'microsoft-sql-server'
)
SELECT
    t.TagName,
    CONVERT(VARCHAR(7), tv.CreationDate, 120) AS Month,
    SUM(tv.ViewCount) AS TotalViews
FROM
    TagViews tv
JOIN
    Tags t ON tv.TagId = t.Id
GROUP BY
    t.TagName, CONVERT(VARCHAR(7), tv.CreationDate, 120)
ORDER BY
    t.TagName, Month

Enter Parameters

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