Please login or register to vote for this query.

(click on this box to dismiss)

Total number of questions and answers for the last 12 months (in 30 day chunks)


Q&A for professional and amateur woodworkers

select p.Id as question_id, p.AnswerCount as ans_num, (case when p.AnswerCount=0 then 0 else 1 end) as is_ans,
(case when p.AcceptedAnswerId is null then 0 else 1 end) as is_accepted ,ans_time, ans_score,
p.CreationDate as cdatetime,p.Score as score, p.ViewCount as view_num,p.FavoriteCount as fav_num,
(case when p.LastEditDate is null then 0 else 1 end) as is_edited,
p.Title as title,p.Tags as tags,p.Body as body,p.CommentCount as cmt_num,cmt_time,cmt_user,
u.Id as user_id,u.Reputation as reputation,badges,gold,silver,bronze,
u.DisplayName as name,u.Location as loc,u.CreationDate as user_cdate
from (Posts p 
left outer join (select q.Id as Id, min(Datediff(s,q.CreationDate,a.CreationDate)) as ans_time,
avg(a.Score) as ans_score
from Posts q left outer join Posts a on q.Id=a.ParentId
where q.PostTypeId=1 and a.PostTypeId=2
group by q.Id)pa on p.Id=pa.Id
left outer join Users u on p.OwnerUserId=u.Id
left outer join (select pb.Id as Id,pb.UserId,(sum(gold)+sum(silver)+sum(bronze)) as badges,
sum(gold) as gold ,sum(silver)as silver,sum(bronze) as bronze
from (select pbp.Id as Id,b.UserId as UserId,(case when b.class=1 then 1 else 0 end) as gold,
(case when b.class=2 then 1 else 0 end) as silver,
(case when b.class=3 then 1 else 0 end) as bronze
from Posts pbp left outer join Badges b on pbp.OwnerUserId=b.UserId) pb
group by pb.Id,pb.UserId)qb on p.Id=qb.Id
left outer join (select pc.Id as Id,min(Datediff(s,pcptime,ctime)) as cmt_time,sum(cmt_user_)as cmt_user
from (select pcp.Id as Id,pcp.CreationDate as pcptime,c.CreationDate as ctime,
(case when pcp.OwnerUserId=c.UserId then 1 else 0 end) as cmt_user_
from Posts pcp left outer join Comments c on pcp.Id=c.PostId) pc
group by pc.Id)qc on p.Id=qc.Id)
where p.PostTypeId=1
and p.Id%50=35
and Year(p.CreationDate)=2019

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results