SELECT *, sum_2024/sum_2023 increase FROM ( SELECT tag_...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT
*,
sum_2024/sum_2023 increase

FROM 
(
    SELECT

    tag_name
    , SUM(
      CASE 
      WHEN yr=2023 THEN cnt
      ELSE 0
      END 
      ) as sum_2023

    , SUM( 
      CASE
      WHEN yr=2024 THEN cnt*(366/cur_yr_ndays)
      ELSE 0
      END
      ) as sum_2024



    FROM 
    (
        SELECT
        tag_name
        , yr
        , cur_yr_ndays
        , count(*) as cnt
        FROM
        (
            SELECT

                  t.TagName as tag_name,
                  YEAR(p.CreationDate) as yr,
                  DATEPART(dayofyear, getdate()) as cur_yr_ndays
            FROM Posts p
            INNER JOIN PostTags pt ON p.Id = pt.PostId
            INNER JOIN Tags t ON pt.TagId = t.Id

          WHERE 
                  YEAR(p.CreationDate) >= 2023
                  AND 
                  t.TagName is not NULL


         ) q0

        GROUP BY
        tag_name
        , yr
        , cur_yr_ndays
    ) q1



    GROUP BY tag_name

) q2

WHERE 
(sum_2024 > 500
or
sum_2023 > 500
)
and sum_2024 > 1 and sum_2023 > 1


ORDER BY sum_2024/sum_2023 DESC

Enter Parameters

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