Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles
WITH PytestTags AS ( -- Find all tags that contain 'pytest' in their name SELECT Id FROM Tags WHERE TagName LIKE 'pytest%' OR TagName LIKE 'py.test%' ), BoostTestTags AS ( -- Find all tags for Boost.Test SELECT Id FROM Tags WHERE TagName = 'boost-test' ), Catch2Tags AS ( -- Find all tags for Catch2 SELECT Id FROM Tags WHERE TagName IN ('catch2', 'catch-unit-test') ), DoctestTags AS ( -- Find all tags for Doctest SELECT Id FROM Tags WHERE TagName = 'doctest' ), GoogleTestTags AS ( -- Find all tags for GoogleTest (googletest or googlemock) SELECT Id FROM Tags WHERE TagName IN ('googletest', 'gmock', 'googlemock', 'gtest') ), PythonTestingPosts AS ( -- at least one testing-related tag, excluding pytest-related tags SELECT pt.PostId FROM PostTags pt JOIN Tags t ON pt.TagId = t.Id WHERE t.TagName IN ('python', 'py', 'pythonic', 'python-shell', 'python-interpreter') INTERSECT SELECT pt.PostId FROM PostTags pt JOIN Tags t ON pt.TagId = t.Id WHERE t.TagName IN ( 'testing', 'test', 'tests', 'software-testing', 'unit-testing', 'unittest', 'unit-test', 'unit-tests', 'automated-tests', 'automated-testing', 'integration-testing', 'integration-tests', 'python-unittest', 'pyunit') EXCEPT -- Exclude any posts that have specific framework-related tags SELECT pt.PostId FROM PostTags pt JOIN PytestTags pytest ON pt.TagId = pytest.Id ), CppTestingPosts AS ( -- at least one testing-related tag, excluding specific testing frameworks for C++ SELECT pt.PostId FROM PostTags pt JOIN Tags t ON pt.TagId = t.Id WHERE t.TagName = 'c++' INTERSECT SELECT pt.PostId FROM PostTags pt JOIN Tags t ON pt.TagId = t.Id WHERE t.TagName IN ( 'testing', 'test', 'tests', 'software-testing', 'unit-testing', 'unittest', 'unit-test', 'unit-tests', 'automated-tests', 'automated-testing', 'integration-testing', 'integration-tests') EXCEPT -- Exclude any posts that have specific C++ testing framework-related tags SELECT pt.PostId FROM PostTags pt JOIN ( SELECT * FROM BoostTestTags UNION ALL SELECT * FROM Catch2Tags UNION ALL SELECT * FROM DoctestTags UNION ALL SELECT * FROM GoogleTestTags ) frameworkTags ON pt.TagId = frameworkTags.Id ), PytestPosts AS ( -- Get all post IDs with pytest-related tags SELECT pt.PostId FROM PostTags pt JOIN PytestTags pytest ON pt.TagId = pytest.Id ), BoostTestPosts AS ( -- Get all post IDs with Boost.Test-related tags SELECT pt.PostId FROM PostTags pt JOIN BoostTestTags boost ON pt.TagId = boost.Id ), Catch2Posts AS ( -- Get all post IDs with Catch2-related tags SELECT pt.PostId FROM PostTags pt JOIN Catch2Tags catch ON pt.TagId = catch.Id ), DoctestPosts AS ( -- Get all post IDs with Doctest-related tags SELECT pt.PostId FROM PostTags pt JOIN DoctestTags doctest ON pt.TagId = doctest.Id ), GoogleTestPosts AS ( -- Get all post IDs with GoogleTest-related tags SELECT pt.PostId FROM PostTags pt JOIN GoogleTestTags gtest ON pt.TagId = gtest.Id ) SELECT YEAR(p.CreationDate) AS Year, -- Year of the posts COUNT(ptp.PostId) AS unittest, -- Count of posts matching the unittest criteria (SELECT COUNT(pp.Id) FROM Posts pp JOIN PytestPosts pytest ON pp.Id = pytest.PostId WHERE YEAR(pp.CreationDate) = YEAR(p.CreationDate) ) AS pytest, -- Count of posts with pytest-related tags (SELECT COUNT(pp.Id) FROM Posts pp JOIN BoostTestPosts boost ON pp.Id = boost.PostId WHERE YEAR(pp.CreationDate) = YEAR(p.CreationDate) ) AS boost_test, -- Count of posts with Boost.Test-related tags (SELECT COUNT(pp.Id) FROM Posts pp JOIN Catch2Posts catch ON pp.Id = catch.PostId WHERE YEAR(pp.CreationDate) = YEAR(p.CreationDate) ) AS catch2, -- Count of posts with Catch2-related tags (SELECT COUNT(pp.Id) FROM Posts pp JOIN DoctestPosts doctest ON pp.Id = doctest.PostId WHERE YEAR(pp.CreationDate) = YEAR(p.CreationDate) ) AS doctest, -- Count of posts with Doctest-related tags (SELECT COUNT(pp.Id) FROM Posts pp JOIN GoogleTestPosts gtest ON pp.Id = gtest.PostId WHERE YEAR(pp.CreationDate) = YEAR(p.CreationDate) ) AS googletest, -- Count of posts with GoogleTest-related tags (SELECT COUNT(pp.Id) FROM Posts pp JOIN CppTestingPosts cpp ON pp.Id = cpp.PostId WHERE YEAR(pp.CreationDate) = YEAR(p.CreationDate) ) AS cpp_testing -- Count of C++ posts with general testing-related tags FROM Posts p JOIN PythonTestingPosts ptp ON p.Id = ptp.PostId WHERE YEAR(p.CreationDate) < YEAR(GETDATE()) -- Exclude current year GROUP BY YEAR(p.CreationDate) ORDER BY Year;