Python Tag Categories

0

Please login or register to vote for this query.

(click on this box to dismiss)

Code Review

Q&A for peer programmer code reviews

--Tag Ids
DECLARE
    @python INT = 3,
    @python_2_6 INT = 1512,
    @python_2_7 INT = 827,
    @python_3_x INT =694;
DECLARE @Question INT = 1;

WITH NoVersionTag AS (
    --Questions tagged with [python]
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId = @python
    --Subtract/except posts tagged with a Python version
    EXCEPT
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question 
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId IN (@python_2_6, @python_2_7, @python_3_x)
),
Python2Tag AS (
    --Questions tagged with [python]
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId = @python
    --Intersect questions tagged with Python 2.7 & 2.6
    INTERSECT
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question 
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId IN (@python_2_6, @python_2_7)
),
Python3Tag AS (
    --Questions tagged with [python]
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId = @python
    --Intersect questions tagged with Python 3.x
    INTERSECT
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question 
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId IN (@python_3_x)
),
BothPython2And3Tags AS (
    --Questions tagged with Python 2.7 & 2.6
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId IN (@python_2_6, @python_2_7)
    --Intersect questions tagged with Python 3.x
    INTERSECT
    SELECT Id
    FROM Posts AS p
    JOIN PostTags AS pt ON p.Id = pt.PostId
    WHERE p.PostTypeId = @Question 
    AND p.ClosedDate IS NULL
    AND p.DeletionDate IS NULL
    AND pt.TagId IN (@python_3_x)
)
SELECT '4. NoVersionTag' AS [Category], COUNT(Id) AS [Posts] 
FROM NoVersionTag
UNION ALL
SELECT '1. Python2Tag' AS [Category], COUNT(Id) AS [Posts] 
FROM Python2Tag
UNION ALL
SELECT '2. Python3Tag' AS [Category], COUNT(Id) AS [Posts] 
FROM Python3Tag
UNION ALL
SELECT '3. BothPython2And3Tags' AS [Category], COUNT(Id) AS [Posts] 
FROM BothPython2And3Tags
ORDER BY [Category] ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results