CREATE TABLE #SecurityTags (Id int, TagName nvarchar(35))...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chemistry

Q&A for scientists, academics, teachers, and students in the field of chemistry

CREATE TABLE #SecurityTags (Id int, TagName nvarchar(35))

INSERT #SecurityTags
SELECT Id, TagName
FROM Tags
WHERE lower(Tags.TagName) like '%auth%'
 OR lower(Tags.TagName) like '%access_log%'
 OR lower(Tags.TagName) like '%backup%'
 OR lower(Tags.TagName) like '%breach%'
 OR lower(Tags.TagName) like '%crypt%'
 OR lower(Tags.TagName) like '%ciph%'
 OR lower(Tags.TagName) like '%crack%ng'
 OR lower(Tags.TagName) like '%forensic%'
 OR lower(Tags.TagName) like '%firewall'
 OR lower(Tags.TagName) like '%hash%'
 OR lower(Tags.TagName) like '%permission%'
 OR lower(Tags.TagName) like '%password%'
 OR lower(Tags.TagName) like '%phish%'
 OR lower(Tags.TagName) like '%priva%'
 OR lower(Tags.TagName) like '%privilege%'
 OR lower(Tags.TagName) like '%recovery%'
 OR lower(Tags.TagName) like '%secur%'
 OR lower(Tags.TagName) like '%token%'
 OR lower(Tags.TagName) like '%vuln%'
 OR lower(Tags.TagName) like '%sql-injection%'
 OR lower(Tags.TagName) like '%malware%'
 OR lower(Tags.TagName) like '%tls%'
 OR lower(Tags.TagName) like '%certificate%'
 OR lower(Tags.TagName) like 'ssl%'
 OR lower(Tags.TagName) like '%ssl'
 OR lower(Tags.TagName) like '%csrf%'
 OR lower(Tags.TagName) like '%trust%'
 OR lower(Tags.TagName) like '%attack%'
 OR lower(Tags.TagName) like '%spoofing'
 OR lower(Tags.TagName) like '%sniffing'
 OR lower(Tags.TagName) like '%virus%'
 OR lower(Tags.TagName) like '%signature%'
 OR lower(Tags.TagName) in ('ddos','xss','antixsslibrary','exploit','memory-leaks',
                           'penetration-testing','data-protection','brute-force',
                           'segmentation-fault','arp','spoof','packet-sniffers',
                           'firewalld','windows-firewall-api','intrusion-detection',
                           'mitmproxy','patch','spyware','trojan','blacklist',
                           'whitelist','denial-of-service','man-in-the-middle',
                           'electronic-signature','digital-signature','xml-signature',
                           'http-signature','gpg-signature','passphrase','risk-analysis',
                           'wpa-supplicant','wpa','stack-overflow','checksum')
                           
CREATE TABLE #SecurityPosts (Id int, Score int, CommentCount int,
                             CreationDate datetime, AcceptedAnswerId int,
                             Title nvarchar(250), Tags nvarchar(250),
                             PostTypeId int, ParentId int)

CREATE TABLE #SecurityQuestions (Id int, Score int, CommentCount int,
                             CreationDate datetime, AcceptedAnswerId int,
                             Title nvarchar(250), Tags nvarchar(250),
                             PostTypeId int, ParentId int)
                             
INSERT #SecurityQuestions
SELECT DISTINCT Posts.Id, Posts.Score, Posts.CommentCount, 
                Posts.CreationDate, Posts.AcceptedAnswerId,
                Posts.Title, Posts.Tags, Posts.PostTypeId, Posts.ParentId
FROM Posts
    INNER JOIN PostTags ON Posts.Id = PostTags.PostId
    INNER JOIN #SecurityTags Tags ON PostTags.TagId = Tags.id   
WHERE Posts.CreationDate >= '2017-10-01'
AND Posts.DeletionDate IS NULL
AND Posts.PostTypeId = 1


INSERT #SecurityPosts
SELECT DISTINCT Posts.Id, Posts.Score, Posts.CommentCount, 
                Posts.CreationDate, Posts.AcceptedAnswerId,
                Posts.Title, Posts.Tags, Posts.PostTypeId, Posts.ParentId
FROM Posts
    INNER JOIN #SecurityQuestions ON Posts.parentId = #SecurityQuestions.Id
WHERE Posts.PostTypeId = 2
AND Posts.DeletionDate IS NULL

INSERT #SecurityPosts
SELECT *
FROM #SecurityQuestions

----

------- 29/9 start
CREATE TABLE #FMScores (Id int, PostTypeId int, ParentId int, FirstMonthScore int)

INSERT #FMScores
SELECT po.Id, po.PostTypeId, po.ParentId,
       isnull(VoteUps.VoteUp,0) - isnull(VoteDowns.VoteDn, 0) FirstMonthScore
FROM #SecurityPosts po
FULL JOIN (SELECT po1.Id, po1.PostTypeId, po1.ParentId, count(*) VoteUp
FROM #SecurityPosts po1
INNER JOIN Votes vup ON vup.PostId = po1.Id and vup.VoteTypeId = 2
WHERE vup.creationdate < po1.creationdate + 30
AND   po1.Creationdate >= '2017-10-01'
GROUP BY po1.Id, po1.PostTypeId, po1.ParentId) VoteUps ON VoteUps.Id = po.Id
FULL JOIN (SELECT po2.Id, po2.PostTypeId, po2.ParentId, count(*) VoteDn
FROM #SecurityPosts po2
INNER JOIN Votes vdn ON vdn.PostId = po2.Id and vdn.VoteTypeId = 3
WHERE vdn.creationdate < po2.creationdate + 30
AND   po2.Creationdate >= '2017-10-01'
GROUP BY po2.Id, po2.PostTypeId, po2.ParentId) VoteDowns ON VoteDowns.Id = po.Id

CREATE TABLE #FMComments (Id int, PostTypeId int, ParentId int, FMComment int)

INSERT #FMComments
SELECT pos.Id, PostTypeId, pos.ParentId, cm.FMComment
FROM #SecurityPosts pos
INNER JOIN (SELECT po1.Id, count(*) FMComment
FROM #SecurityPosts po1
INNER JOIN Comments c ON c.PostId = po1.Id
WHERE c.creationdate < po1.creationdate + 30
AND   po1.Creationdate >= '2017-10-01'
GROUP BY po1.Id
) cm ON cm.Id = pos.Id


SELECT *
FROM
(
SELECT q1.id, q1.questiondate, q1.answerdate, q1.responsehours,
q1.title, q1.tags, q2.Score, isnull(fms.FirstMonthScore,0) FirstMonthScore, 
       q2.CommentCount, isnull(fmc.FMCommentCount,0) FirstMonthCommentCount,'SSE' Source
FROM
(SELECT q.id, q.creationdate questiondate, a.creationdate answerdate, 
    	 datediff(hh, q.creationdate, a.creationdate) responsehours,
    	 q.title, q.tags
FROM  Posts q
INNER JOIN
      Posts a ON q.AcceptedAnswerId = a.Id
WHERE q.creationdate >= '2017-10-01'
AND   q.PostTypeId = 1
AND   a.PostTypeId = 2
UNION
SELECT qna.id, qna.creationdate questiondate, lans.creationdate answerdate, 
       datediff(hh, qna.creationdate, lans.creationdate) responsehours,
      qna.title, qna.tags
FROM  Posts qna
FULL JOIN 
(SELECT ans.parentid, max(creationdate) creationdate
FROM  Posts ans
WHERE ans.PostTypeId = 2
GROUP BY ans.parentid
) lans ON qna.id = lans.parentid
WHERE qna.creationdate >= '2017-10-01'
AND   qna.PostTypeId = 1
AND   qna.AcceptedAnswerId IS NULL
) q1
INNER JOIN
(SELECT question.id, 
        question.Score + isnull(answer.Score, 0) Score,
        question.CommentCount + isnull(answer.CommentCount, 0) CommentCount
FROM
(SELECT q.id, q.CreationDate,
    	q.Score,
    	q.CommentCount
FROM Posts q
WHERE q.PostTypeId = 1
) question
FULL JOIN
(SELECT a.parentid, 
    SUM(a.Score) Score,
    SUM(a.CommentCount) CommentCount
FROM Posts a
WHERE a.ParentId is not NULL
AND   a.PostTypeId = 2
GROUP BY a.parentid) answer ON question.id = answer.parentid
WHERE question.CreationDate >= '2017-10-01'
) q2 ON q1.id = q2.id
INNER JOIN
(SELECT question.id, 
        question.FirstMonthScore + isnull(answer.FirstMonthScore, 0) FirstMonthScore
FROM
(SELECT q.id, 
    	q.FirstMonthScore
FROM #FMScores q
WHERE q.PostTypeId = 1
) question
FULL JOIN
(SELECT a.parentid, 
    SUM(a.FirstMonthScore) FirstMonthScore
FROM #FMScores a
WHERE a.ParentId is not NULL
AND   a.PostTypeId = 2
GROUP BY a.parentid) answer ON question.id = answer.parentid
) fms ON q1.id = fms.id
FULL JOIN
(SELECT question.id, 
isnull(question.FMComment,0) + isnull(answer.FMComment, 0) FMCommentCount
FROM
(SELECT q.id, 
    	q.FMComment
FROM #FMComments q
WHERE q.PostTypeId = 1
) question
INNER JOIN
(SELECT a.parentid, 
    SUM(a.FMComment) FMComment
FROM #FMComments a
WHERE a.ParentId is not NULL
AND   a.PostTypeId = 2
GROUP BY a.parentid) answer ON question.id = answer.parentid
) fmc ON q1.id = fmc.id
) t
WHERE t.questionDate >= '2020-10-01' AND t.questionDate < '2021-10-01'
-- t.questionDate >= '2021-10-01' AND t.questionDate < '2022-10-01' --> 44774 
--t.questionDate >= '2020-10-01' AND t.questionDate < '2021-10-01' --> 39684 
--t.questionDate >= '2019-10-01' AND t.questionDate < '2020-10-01' --> 46738 
--t.questionDate >= '2018-10-01' AND t.questionDate < '2019-10-01' --> 40772
-- t.questionDate >= '2017-10-01' AND t.questionDate < '2018-10-01' --> 47473

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results