0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

WITH MatchedTags AS (
    SELECT DISTINCT TagName
    FROM Tags
    WHERE 
        -- Red Hat and Associated Products
        TagName = 'red-hat' OR
        TagName = 'rhel' OR
        TagName = 'openshift' OR
        TagName = 'ansible' OR
        TagName = 'satellite' OR
        TagName = 'openstack' OR
        TagName = 'jboss' OR
        
        -- HashiCorp and Associated Products
        TagName = 'hashicorp' OR
        TagName = 'terraform' OR
        TagName = 'vault' OR
        TagName = 'consul' OR
        TagName = 'nomad' OR
        TagName = 'boundary' OR
        TagName = 'waypoint' OR
        
        -- GitLab and Associated Products
        TagName = 'gitlab' OR
        TagName = 'ci' OR
        TagName = 'cd' OR
        TagName = 'scm' OR
        TagName = 'pipeline' OR
        TagName = 'devops'
)

SELECT 
    t.TagName AS CompanyOrProduct,
    COUNT(p.Id) AS QuestionCount,
    DATEPART(YEAR, p.CreationDate) AS Year
FROM 
    Posts p
JOIN 
    PostTags pt ON p.Id = pt.PostId
JOIN 
    Tags t ON pt.TagId = t.Id
WHERE 
    t.TagName IN (SELECT TagName FROM MatchedTags)
    AND p.PostTypeId = 1 -- Questions only
    AND DATEPART(YEAR, p.CreationDate) IN (2023, 2024) -- Filter by year
GROUP BY 
    t.TagName, DATEPART(YEAR, p.CreationDate)
ORDER BY 
    CompanyOrProduct, Year;

fork query

edited dec 13

created dec 12 anonymous