select gr_month.TagName as Tag, row_number() over (order ...

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 
       gr_month.TagName as Tag,
       row_number() over (order by gr_month.jan17 desc) as rank_jan17,
       row_number() over (order by gr_month.feb17 desc) as rank_feb17,
       row_number() over (order by gr_month.mar17 desc) as rank_mar17,
       row_number() over (order by gr_month.apr17 desc) as rank_apr17,
       row_number() over (order by gr_month.may17 desc) as rank_may17,
       row_number() over (order by gr_month.jun17 desc) as rank_jun17,
       row_number() over (order by gr_month.jul17 desc) as rank_jul17,
       row_number() over (order by gr_month.aug17 desc) as rank_aug17,
       row_number() over (order by gr_month.sep17 desc) as rank_sep17,
       row_number() over (order by gr_month.oct17 desc) as rank_oct17,
       row_number() over (order by gr_month.nov17 desc) as rank_nov17,
       row_number() over (order by gr_month.dec17 desc) as rank_dec17,
       row_number() over (order by gr_month.jan18 desc) as rank_jan18,
       row_number() over (order by gr_month.feb18 desc) as rank_feb18,
       row_number() over (order by gr_month.mar18 desc) as rank_mar18,
       row_number() over (order by gr_month.apr18 desc) as rank_apr18,
       row_number() over (order by gr_month.may18 desc) as rank_may18,
       row_number() over (order by gr_month.jun18 desc) as rank_jun18,
       row_number() over (order by gr_month.jul18 desc) as rank_jul18,
       row_number() over (order by gr_month.aug18 desc) as rank_aug18,
       row_number() over (order by gr_month.sep18 desc) as rank_sep18,
       row_number() over (order by gr_month.oct18 desc) as rank_oct18,
       row_number() over (order by gr_month.nov18 desc) as rank_nov18,
       row_number() over (order by gr_month.dec18 desc) as rank_dec18,
       row_number() over (order by gr_month.jan19 desc) as rank_jan19,
       row_number() over (order by gr_month.feb19 desc) as rank_feb19,
       row_number() over (order by gr_month.mar19 desc) as rank_mar19,
       row_number() over (order by gr_month.apr19 desc) as rank_apr19,
       gr_month.jan17 as q_jan17,
       gr_month.feb17 as q_feb17,
       gr_month.mar17 as q_mar17,
       gr_month.apr17 as q_apr17,
       gr_month.may17 as q_may17,
       gr_month.jun17 as q_jun17,
       gr_month.jul17 as q_jul17,
       gr_month.aug17 as q_aug17,
       gr_month.sep17 as q_sep17,
       gr_month.oct17 as q_oct17,
       gr_month.nov17 as q_nov17,
       gr_month.dec17 as q_dec17,
       gr_month.jan18 as q_jan18,
       gr_month.feb18 as q_feb18,
       gr_month.mar18 as q_mar18,
       gr_month.apr18 as q_apr18,
       gr_month.may18 as q_may18,
       gr_month.jun18 as q_jun18,
       gr_month.jul18 as q_jul18,
       gr_month.aug18 as q_aug18,
       gr_month.sep18 as q_sep18,
       gr_month.oct18 as q_oct18,
       gr_month.nov18 as q_nov18,
       gr_month.dec18 as q_dec18,
       gr_month.jan19 as q_jan19,
       gr_month.feb19 as q_feb19,
       gr_month.mar19 as q_mar19,
       gr_month.apr19 as q_apr19

from

(select
COUNT(CASE WHEN Posts.CreationDate < '2017-02-01' and Posts.CreationDate >= '2017-01-01' then 1 ELSE NULL END) as jan17,
COUNT(CASE WHEN Posts.CreationDate < '2017-03-01' and Posts.CreationDate >= '2017-02-01' then 1 ELSE NULL END) as feb17,
COUNT(CASE WHEN Posts.CreationDate < '2017-04-01' and Posts.CreationDate >= '2017-03-01' then 1 ELSE NULL END) as mar17,
COUNT(CASE WHEN Posts.CreationDate < '2017-05-01' and Posts.CreationDate >= '2017-04-01' then 1 ELSE NULL END) as apr17,
COUNT(CASE WHEN Posts.CreationDate < '2017-06-01' and Posts.CreationDate >= '2017-05-01' then 1 ELSE NULL END) as may17, 
COUNT(CASE WHEN Posts.CreationDate < '2017-07-01' and Posts.CreationDate >= '2017-06-01' then 1 ELSE NULL END) as jun17, 
COUNT(CASE WHEN Posts.CreationDate < '2017-08-01' and Posts.CreationDate >= '2017-07-01' then 1 ELSE NULL END) as jul17, 
COUNT(CASE WHEN Posts.CreationDate < '2017-09-01' and Posts.CreationDate >= '2017-08-01' then 1 ELSE NULL END) as aug17, 
COUNT(CASE WHEN Posts.CreationDate < '2017-10-01' and Posts.CreationDate >= '2017-09-01' then 1 ELSE NULL END) as sep17, 
COUNT(CASE WHEN Posts.CreationDate < '2017-11-01' and Posts.CreationDate >= '2017-10-01' then 1 ELSE NULL END) as oct17, 
COUNT(CASE WHEN Posts.CreationDate < '2017-12-01' and Posts.CreationDate >= '2017-11-01' then 1 ELSE NULL END) as nov17, 
COUNT(CASE WHEN Posts.CreationDate < '2018-01-01' and Posts.CreationDate >= '2017-12-01' then 1 ELSE NULL END) as dec17, 
COUNT(CASE WHEN Posts.CreationDate < '2018-02-01' and Posts.CreationDate >= '2018-01-01' then 1 ELSE NULL END) as jan18, 
COUNT(CASE WHEN Posts.CreationDate < '2018-03-01' and Posts.CreationDate >= '2018-02-01' then 1 ELSE NULL END) as feb18, 
COUNT(CASE WHEN Posts.CreationDate < '2018-04-01' and Posts.CreationDate >= '2018-03-01' then 1 ELSE NULL END) as mar18, 
COUNT(CASE WHEN Posts.CreationDate < '2018-05-01' and Posts.CreationDate >= '2018-04-01' then 1 ELSE NULL END) as apr18, 
COUNT(CASE WHEN Posts.CreationDate < '2018-06-01' and Posts.CreationDate >= '2018-05-01' then 1 ELSE NULL END) as may18, 
COUNT(CASE WHEN Posts.CreationDate < '2018-07-01' and Posts.CreationDate >= '2018-06-01' then 1 ELSE NULL END) as jun18,
COUNT(CASE WHEN Posts.CreationDate < '2018-08-01' and Posts.CreationDate >= '2018-07-01' then 1 ELSE NULL END) as jul18,
COUNT(CASE WHEN Posts.CreationDate < '2018-09-01' and Posts.CreationDate >= '2018-08-01' then 1 ELSE NULL END) as aug18,
COUNT(CASE WHEN Posts.CreationDate < '2018-10-01' and Posts.CreationDate >= '2018-09-01' then 1 ELSE NULL END) as sep18,
COUNT(CASE WHEN Posts.CreationDate < '2018-11-01' and Posts.CreationDate >= '2018-10-01' then 1 ELSE NULL END) as oct18,
COUNT(CASE WHEN Posts.CreationDate < '2018-12-01' and Posts.CreationDate >= '2018-11-01' then 1 ELSE NULL END) as nov18,
COUNT(CASE WHEN Posts.CreationDate < '2019-01-01' and Posts.CreationDate >= '2018-12-01' then 1 ELSE NULL END) as dec18,
COUNT(CASE WHEN Posts.CreationDate < '2019-02-01' and Posts.CreationDate >= '2019-01-01' then 1 ELSE NULL END) as jan19,
COUNT(CASE WHEN Posts.CreationDate < '2019-03-01' and Posts.CreationDate >= '2019-02-01' then 1 ELSE NULL END) as feb19,
COUNT(CASE WHEN Posts.CreationDate < '2019-04-01' and Posts.CreationDate >= '2019-03-01' then 1 ELSE NULL END) as mar19,
COUNT(CASE WHEN Posts.CreationDate < '2019-05-01' and Posts.CreationDate >= '2019-04-01' then 1 ELSE NULL END) as apr19,
TagName
from Tags, PostTags, Posts
where Tags.Id = PostTags.TagId and Posts.Id = PostId
    and Posts.CreationDate < '2019-05-01'
    and Posts.CreationDate >= '2017-01-01'
group by TagName) as gr_month

order by rank_apr19 asc

Enter Parameters

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