## Bounty stats. What can a Bounty do for you?

3

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

``````CREATE TABLE #Questions(
[Id] [bigint] NOT NULL,
[Score] [bigint] NULL,
[ViewCount] [bigint] NULL,
[BountyNr] [bigint] NULL
)
CREATE INDEX IX_Questions_Id ON #Questions (Id);
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
,Score
,ViewCount
,BountyNr = (
SELECT COUNT(*)
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
)
), 'With' = (
(SELECT COUNT(*)
FROM #Questions
WHERE BountyNr>0
)

)

INSERT INTO #QuestionTotals
(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)
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)
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``````

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached