0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientific skepticism

select
creation_yearmonth
--Calculate the percentage of posts that contain a tag about D&D
,count(distinct case when dnd = 1 then postid end)/
cast(count(distinct postid) as double precision) as dnd_percentage
--,count(distinct case when dnd = 1 then postid end) as dnd_posts
--,count(distinct postid) as total_posts
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,
  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
  )a
  group by creation_yearmonth
        order by creation_yearmonth ASC

view current version

edited jan 30

created jan 27 Lio.Elbammalf