0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientific skepticism

select
creation_yearmonth

,count(distinct case when dnd = 1 then postid end)/
cast(count(distinct postid) as double precision) as dnd_percentage_posts

--,count(distinct case when dnd = 1 then postid end) as dnd_posts
--,count(distinct postid) as total_posts

,sum(case when dnd=1 then viewcount end)/
sum(viewcount) as dnd_percentage_views


--,sum(case when dnd=1 then viewcount end) as dnd_post_views
--,sum(viewcount) as total_post_views
from(
--Check whether one of the tags on a post is related to D&D
select
  cast( concat(year(p.creationDate),
  '-',
  month(p.creationDate)
  ,'-01') as Date) as creation_yearmonth,
  pt.postid,
  p.viewcount,
  max(case when t.tagname like '%dnd%' then 1 else 0 end) as dnd
    from posttags pt 
    join tags t on pt.tagid = t.id 
    join posts p on pt.postid = p.id
--    where creationDate>='2020-01-01'
    group by   cast( concat(year(p.creationDate),
  '-',
  month(p.creationDate)
  ,'-01') as Date),pt.postid,p.viewcount
  )a
  group by creation_yearmonth
        order by creation_yearmonth ASC

view current version

edited jan 30

created jan 27 Lio.Elbammalf