Does the bounty system work?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

with bounty_answers as (
  select 1.0*a.Score Score,
         case when q.AcceptedAnswerId = a.Id then 1 else 0 end accepted,
         case 
           when v.CreationDate is null          then 'none'
           when a.CreationDate < v.CreationDate then 'before'
           when a.CreationDate > v.CreationDate then 'after'
         end rel_date,
         (select count(*) 
          from Votes b
               join VoteTypes bt on b.VoteTypeId = bt.Id
          where bt.Name = 'BountyClose'
                and a.Id = PostId) bounty_award
  from Posts q
       join PostsWithDeleted a on a.ParentId = q.Id
       join Votes v on v.PostId = q.Id 
       join VoteTypes vt on vt.Id = v.VoteTypeId and vt.Name = 'BountyStart'
  where q.PostTypeId = 1
        and a.PostTypeId = 2
        
--        and q.Id = 306705
)

select rel_date,
       count(*) N,
       round(avg(Score), 1) avg_score,
       round(1.0*sum(bounty_award)/count(*), 3) bountied_rate,
       round(1.0*sum(accepted)/count(*), 3) accepted_rate     
from bounty_answers
group by rel_date
order by rel_date desc;

with all_answers as (
  select 1.0*a.Score Score,
         (select count(*) 
          from Votes b
               join VoteTypes bt on b.VoteTypeId = bt.Id
          where bt.Name = 'BountyClose'
                and a.Id = PostId) bounty_award,
         case when q.AcceptedAnswerId = a.Id then 1 else 0 end accepted
  from Posts q
       join PostsWithDeleted a on a.ParentId = q.Id
  where q.PostTypeId = 1
        and a.PostTypeId = 2
)
select 
       count(*) N,
       round(avg(Score), 1) avg_score,
       round(1.0*sum(bounty_award)/count(*), 3) bountied_rate,
       round(1.0*sum(accepted)/count(*), 3) accepted_rate    
from all_answers;

with bounty_questions as (
  select max(1.0*a.Score) max_score,
         avg(1.0*a.Score) avg_score,
         case when q.AcceptedAnswerId is not null then 1.0 else 0 end accepted,
         1.0*(select count(*)
          from Votes v 
               join VoteTypes vt on vt.Id = v.VoteTypeId
          where vt.Name = 'BountyStart'
                and q.Id = v.PostId
         ) bounties,
         1.0*q.ViewCount views
  from PostsWithDeleted q
       join PostsWithDeleted a on a.ParentId = q.Id
  where q.PostTypeId = 1
        and a.PostTypeId = 2
  group by q.Id, q.AcceptedAnswerId, q.ViewCount
)

select 
       count(*) N,
       round(avg(avg_score), 1) avg_score,
       round(avg(max_score), 2) avg_max_score,
       round(avg(bounties), 3) avg_bounties,
       round(avg(accepted), 3) accept_rate,
       round(avg(views), 0) avg_views
from bounty_questions
where bounties > 0;

with all_questions as (
  select max(1.0*a.Score) max_score,
         avg(1.0*a.Score) avg_score,
         case when q.AcceptedAnswerId is not null then 1.0 else 0 end accepted,
         1.0*q.ViewCount views
  from PostsWithDeleted q
       join PostsWithDeleted a on a.ParentId = q.Id
  where q.PostTypeId = 1
        and a.PostTypeId = 2
  group by q.Id, q.AcceptedAnswerId, q.ViewCount
)

select 
       count(*) N,
       round(avg(avg_score), 1) avg_score,
       round(avg(max_score), 2) avg_max_score,
       round(avg(accepted), 3) accept_rate,
       round(avg(views), 0) avg_views
from all_questions;

Enter Parameters

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