Bounty stats. What can a Bounty do for you?

Please login or register to vote for this query.

(click on this box to dismiss)
Q&A about the site for Drupal developers and administrators
CREATE TABLE #Questions(
	[Id] [bigint] NOT NULL,
	[AcceptedAnswerId] [bigint] NULL,
	[Score] [bigint] NULL,
	[ViewCount] [bigint] NULL,
	[BountyNr] [bigint] NULL
)
CREATE INDEX IX_Questions_Id ON #Questions (Id);
CREATE INDEX IX_Questions_AcceptedAnswerId ON #Questions (AcceptedAnswerId);
CREATE INDEX IX_Questions_Score ON #Questions (Score);
CREATE INDEX IX_Questions_ViewCount ON #Questions (ViewCount);
CREATE INDEX IX_Questions_BountyNr ON #Questions (BountyNr);


INSERT INTO #Questions
SELECT 
    Id
    ,AcceptedAnswerId
    ,Score
    ,ViewCount
    ,BountyNr = (
		SELECT COUNT(*)
		FROM Votes
		WHERE PostId=P.Id
		AND VoteTypeId=8
    )
FROM Posts P
WHERE PostTypeId=1
AND DATEDIFF(yy, CreationDate, GetDate())=0



CREATE TABLE #QuestionTotals (
	[ID] [bigint] NOT NULL,
	[Name] [Nvarchar](1000) NULL,
	[NoBounty] [DECIMAL](10,2) NULL,
	[Bounty] [DECIMAL](10,2) NULL
)
CREATE INDEX IX_QuestionTotals_Id ON #QuestionTotals (Id);
CREATE INDEX IX_QuestionTotals_NoBounty ON #QuestionTotals (NoBounty);
CREATE INDEX IX_QuestionTotals_Bounty ON #QuestionTotals (Bounty);

print '---' + CHAR(10) + 'DATA' + CHAR(10) + '---' 

INSERT INTO #QuestionTotals
SELECT 1, 'Questions (total)', 'Without' = (
  SELECT COUNT(*) FROM #Questions WHERE BountyNr=0
), 'With' = (
  SELECT COUNT(*) FROM #Questions WHERE BountyNr>0
)
     
INSERT INTO #QuestionTotals   
SELECT 2, 'Questions (viewcount)', 'Without' = (
  SELECT SUM(ViewCount) FROM #Questions WHERE BountyNr=0
), 'With' = (
  SELECT SUM(ViewCount) FROM #Questions WHERE BountyNr>0
)
  
INSERT INTO #QuestionTotals
SELECT 3, 'Questions (score)', 'Without' = (
  SELECT SUM(Score) FROM #Questions WHERE BountyNr=0
), 'with' = (
  SELECT SUM(Score) FROM #Questions WHERE BountyNr>0
)

INSERT INTO #QuestionTotals
SELECT 4, 'Questions (viewcount)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=2)--viewcount
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=1)--questions
), 'with' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=2)--viewcount
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=1)--questions

)

INSERT INTO #QuestionTotals
SELECT 5, 'Questions (score)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=3)--score
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=1)--questions
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=3)--score
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=1)--questions

)


INSERT INTO #QuestionTotals
SELECT 6, 'Answers (total)', 'Without' = (
  (SELECT COUNT(*) 
    FROM Posts 
    WHERE ParentId IN (
      SELECT Id FROM #Questions WHERE BountyNr=0      
    )
  )
), 'With' =  (
  (SELECT COUNT(*)
    FROM Posts 
    WHERE ParentId IN (
      SELECT Id FROM #Questions WHERE BountyNr>0      
    )
  )
)


INSERT INTO #QuestionTotals
SELECT 7, 'Answers (score)', 'Without' = (
  (SELECT SUM(Score) 
    FROM Posts 
    WHERE ParentId IN (
      SELECT Id FROM #Questions WHERE BountyNr=0      
    )
  )
), 'With' =  (
  (SELECT SUM(Score) 
    FROM Posts 
    WHERE ParentId IN (
      SELECT Id FROM #Questions WHERE BountyNr>0      
    )
  )
)


INSERT INTO #QuestionTotals
SELECT 8, 'Answers (score)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=7)--score
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=6)--answers
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=7)--score
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=6)--answers

)


INSERT INTO #QuestionTotals
SELECT 9, 'Answers (accepted)', 'Without' = (
  (SELECT COUNT(*) 
    FROM #Questions 
    WHERE BountyNr=0
    AND AcceptedAnswerId IS NOT NULL
  )
), 'With' = (
  (SELECT COUNT(*) 
    FROM #Questions 
    WHERE BountyNr>0      
    AND AcceptedAnswerId IS NOT NULL
  )

)


INSERT INTO #QuestionTotals
SELECT 10, 'Answers-answers (accepted)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=9)--accepted
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=6)--accepted
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=9)--accepted
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=6)--accepted

)

INSERT INTO #QuestionTotals
SELECT 11, 'Answers-questions (accepted)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=9)--accepted
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=1)--questions
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=9)--accepted
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=1)--questions

)

INSERT INTO #QuestionTotals
SELECT 12, 'Bounty set (total)', 'Without' = (
  0
), 'With' = (
  SELECT SUM(BountyAmount) 
  FROM Votes
  WHERE VoteTypeId=8
  AND PostId IN (
      SELECT Id FROM #Questions WHERE BountyNr>0     
  )

)

INSERT INTO #QuestionTotals
SELECT 13, 'Bounty given (total)', 'Without' = (
  0
), 'With' = (
  SELECT SUM(BountyAmount) 
  FROM Votes
  WHERE VoteTypeId=9
  AND PostId IN (
      SELECT Id
      FROM Posts
      WHERE PostTypeId=2
      AND ParentId IN (
        SELECT Id FROM #Questions WHERE BountyNr>0     
      )
  )
)

INSERT INTO #QuestionTotals
SELECT 14, 'Bounty lost (total)', 'Without' = (
  0
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=12)
    -(SELECT Bounty FROM #QuestionTotals WHERE Id=13)
)

INSERT INTO #QuestionTotals
SELECT 15, 'Questions (reputation)', 'Without' = (
  5*(SELECT NoBounty FROM #QuestionTotals WHERE Id=3)
), 'with' = (
  5
  *(SELECT Bounty FROM #QuestionTotals WHERE Id=3)
  -(SELECT Bounty FROM #QuestionTotals WHERE Id=12)
)


INSERT INTO #QuestionTotals
SELECT 16, 'Questions (reputation)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=15)--rep
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=1)--questions
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=15)--rep
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=1)--questions

)


INSERT INTO #QuestionTotals
SELECT 17, 'Answers (reputation)', 'Without' = (
  10*(SELECT NoBounty FROM #QuestionTotals WHERE Id=7)
  +(SELECT NoBounty FROM #QuestionTotals WHERE Id=9)*15
), 'with' = (
  10
  *(SELECT Bounty FROM #QuestionTotals WHERE Id=7)
  +(SELECT Bounty FROM #QuestionTotals WHERE Id=13)
  +(SELECT Bounty FROM #QuestionTotals WHERE Id=9)*15
)


INSERT INTO #QuestionTotals
SELECT 18, 'Answers (reputation)(AVG)', 'Without' = (
  (SELECT NoBounty FROM #QuestionTotals WHERE Id=17)--rep
  / (SELECT NoBounty FROM #QuestionTotals WHERE Id=6)--answers
), 'With' = (
  (SELECT Bounty FROM #QuestionTotals WHERE Id=17)--rep
  / (SELECT Bounty FROM #QuestionTotals WHERE Id=6)--answers

)

SELECT Id, Name, NoBounty, Bounty
FROM #QuestionTotals
ORDER BY Id


print '---' + CHAR(10) + 'CONCLUSIONS' + CHAR(10) + '---' 

CREATE TABLE #conclusion(
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Content] [nvarchar](255) NULL,
 CONSTRAINT [PK_conclusion_1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

DECLARE @spentRep bigint, @payedRep bigint, @lostRep bigint, @percentRep decimal(10,2)
SET @spentRep = (SELECT TOP 1 Bounty FROM #QuestionTotals WHERE Id=12)
SET @payedRep = (SELECT TOP 1 Bounty FROM #QuestionTotals WHERE Id=13)
SET @lostRep = (SELECT TOP 1 Bounty FROM #QuestionTotals WHERE Id=14)
SET @percentRep = (CAST(@lostRep AS DECIMAL)/@spentRep)*100

DECLARE @percentVC decimal(10,2),@percentSCORE decimal(10,2), @percentQREP decimal(10,2), @percentAREP decimal(10,2)
SET @percentVC = (SELECT TOP 1 (Bounty/NoBounty)*100 FROM #QuestionTotals WHERE Id=4)
SET @percentSCORE = (SELECT TOP 1 (Bounty/NoBounty)*100 FROM #QuestionTotals WHERE Id=5)
SET @percentQREP = (SELECT TOP 1 (Bounty/NoBounty)*100 FROM #QuestionTotals WHERE Id=16)
SET @percentAREP = (SELECT TOP 1 (Bounty/NoBounty)*100 FROM #QuestionTotals WHERE Id=18)


INSERT INTO #conclusion
SELECT 'We spent '+CAST(@spentRep AS nvarchar(10))+' reputation to start bounties last year.'

INSERT INTO #conclusion
SELECT 'We received '+CAST(@payedRep AS nvarchar(10))+' reputation back from stackoverflow.'

INSERT INTO #conclusion
SELECT 'Stackoverflow''s profit: '+CAST(@lostRep AS nvarchar(10))

INSERT INTO #conclusion
SELECT 'We lost '+CAST(@percentRep AS nvarchar(10))+'% of our reputation in the process.'

INSERT INTO #conclusion SELECT ''

INSERT INTO #conclusion
SELECT 'Starting a bounty will increase viewcount with '+CAST(@percentVC AS nvarchar(10))+'%'

INSERT INTO #conclusion
SELECT 'Starting a bounty will increase question score with '+CAST(@percentSCORE AS nvarchar(10))+'%'

INSERT INTO #conclusion
SELECT 'Starting a bounty will increase question reputation with '+CAST(@percentQREP AS nvarchar(10))+'%'

INSERT INTO #conclusion
SELECT 'Starting a bounty will increase answer reputation with '+CAST(@percentAREP AS nvarchar(10))+'%'




SELECT (CASE WHEN LEN(content)>0 THEN ' - ' ELSE '' END) + content
FROM #conclusion
ORDER BY Id


DROP TABLE #Questions
DROP TABLE #QuestionTotals
DROP TABLE #conclusion
edited may 23 14
created may 23 14
Hugo Delsing
Enter Parameters

Options: Text-only results Include execution plan
Switch to main site
Hold tight while we fetch your results
Results[:current/:total] Messages Graph Execution Plan
Download CSV Download XML
:records returned in :time ms:cached