0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

DECLARE @StartDate DATE = '2020-12-01';
DECLARE @EndDate DATE = '2023-12-01';

WITH TaggedQuestions AS (
  SELECT
    Id,
    CreationDate,
    CASE WHEN CHARINDEX('<spring>', Tags) > 0 THEN 1 ELSE 0 END AS SpringTag,
    CASE WHEN CHARINDEX('<spring-boot>', Tags) > 0 THEN 1 ELSE 0 END AS SpringBootTag,
    CASE WHEN CHARINDEX('<jakarta-ee>', Tags) > 0 THEN 1 ELSE 0 END AS JakartaEETag,
    CASE WHEN CHARINDEX('<quarkus>', Tags) > 0 THEN 1 ELSE 0 END AS QuarkusTag

FROM PostsWithDeleted
  WHERE
    PostTypeId = 1 AND 
    CreationDate >= @StartDate AND
    CreationDate <= @EndDate
),
MonthlyCounts AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    SUM(SpringTag) AS Spring,
    SUM(SpringBootTag) AS SpringBoot,
    SUM(JakartaEETag) AS JakartaEE,
    SUM(QuarkusTag) AS Quarkus
  FROM TaggedQuestions
  GROUP BY DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
)
SELECT
  Month,
  Spring,
  SpringBoot,
  JakartaEE,
  Quarkus
FROM MonthlyCounts
ORDER BY Month;

fork query

edited dec 5

created dec 5 anonymous