With tags_python as (SELECT DISTINCT DATEPART(year, p.cr...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

Q&A for scientific skepticism

With tags_python as 
(SELECT  DISTINCT DATEPART(year, p.creationdate) as creat_year,
         count(p.tags) OVER (PARTITION BY DATEPART(year, p.creationdate)) as count_python
  from posts p
  where tags LIKE '%python%'
      and DATEPART(year, p.creationdate)>=2020 
      and DATEPART(year, p.creationdate)<=2023 ),
      
tags_sql as 
(SELECT  DISTINCT DATEPART(year, p.creationdate) as creat_year,
         count(p.tags) OVER (PARTITION BY DATEPART(year, p.creationdate)) as count_sql
  from posts p
  where tags LIKE '%sql%'
      and DATEPART(year, p.creationdate)>=2020 
      and DATEPART(year, p.creationdate)<=2023), 
      
tags_javascript as 
(SELECT  DISTINCT DATEPART(year, p.creationdate) as creat_year,
         count(p.tags) OVER (PARTITION BY DATEPART(year, p.creationdate)) as count_javascript
  from posts p
  where tags LIKE '%javascript%'
      and DATEPART(year, p.creationdate)>=2020 
      and DATEPART(year, p.creationdate)<=2023),
      
tags_php as 
(SELECT  DISTINCT DATEPART(year, p.creationdate) as creat_year,
         count(p.tags) OVER (PARTITION BY DATEPART(year, p.creationdate)) as count_php
  from posts p
  where tags LIKE '%php%'
      and DATEPART(year, p.creationdate)>=2020 
      and DATEPART(year, p.creationdate)<=2023),
      
tags_c as 
(SELECT  DISTINCT DATEPART(year, p.creationdate) as creat_year,
         count(p.tags) OVER (PARTITION BY DATEPART(year, p.creationdate)) as count_c
  from posts p
  where tags LIKE '%c++%'
      and DATEPART(year, p.creationdate)>=2020 
      and DATEPART(year, p.creationdate)<=2023)

SELECT py.creat_year,
       py.count_python as PYTHON,
       sql.count_sql as SQL,
       js.count_javascript as JavaScript,
       php.count_php as PHP,
       c.count_c as Cplus,
       lag(count_python) over (partition by py.creat_year order by py.creat_year)
      from tags_python py
      join tags_sql sql on sql.creat_year = py.creat_year
      join tags_javascript js on js.creat_year = sql.creat_year
      join tags_php php on php.creat_year = sql.creat_year
      join tags_c c on c.creat_year = sql.creat_year
order by py.creat_year

Enter Parameters

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