Anatomy of a Great Question

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

-- Anatomy of a Great Question

DECLARE @Total INT;
DECLARE @UpVotes INT;
DECLARE @DownVotes INT;
DECLARE @Reputation INT;

SELECT
    @Total = COUNT(*) 

FROM
    Posts

WHERE
    Score > 100 --great
AND PostTypeId = 1 --question
AND CommunityOwnedDate IS NULL
AND OwnerUserId IS NOT NULL



--user reputation
SELECT
    @Reputation = AVG( Users.Reputation ) 

FROM
    Posts

    INNER JOIN
    Users
    ON Posts.OwnerUserId = Users.Id

WHERE
        Score              > 100 --great
    AND PostTypeId         = 1 --question
    AND CommunityOwnedDate IS NULL


--up votes
SELECT
    @UpVotes = COUNT(*) / @Total

FROM
    Posts

    INNER JOIN
    Votes
    ON Posts.Id = Votes.PostId

WHERE
        Score              > 100 --great
    AND PostTypeId         = 1 --question
    AND CommunityOwnedDate IS NULL
    AND Votes.VoteTypeId   = 2 --up
    AND OwnerUserId IS NOT NULL



--down votes
SELECT
    @DownVotes = COUNT(*) / @Total

FROM
    Posts

    INNER JOIN
    Votes
    ON Posts.Id = Votes.PostId

WHERE
        Score              > 100 --great
    AND PostTypeId         = 1 --question
    AND CommunityOwnedDate IS NULL
    AND Votes.VoteTypeId   = 3 --down
    AND OwnerUserId IS NOT NULL



PRINT @Total


SELECT
    @Total AS [Great Questions],
    AVG( Score ) AS Score,
    AVG( CommentCount ) AS Comments,
    ABS( AVG( DATEDIFF( M, GETDATE(), CreationDate ) ) ) AS [Months Old],
    @UpVotes AS UpVotes,
    @DownVotes AS DownVotes,
    @Reputation AS [Reputation of OP]
    
FROM
    Posts

WHERE
        Score              > 100 --great
    AND PostTypeId         = 1 --question
    AND CommunityOwnedDate IS NULL
    AND OwnerUserId IS NOT NULL

Enter Parameters

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