0

Please login or register to vote for this query.

(click on this box to dismiss)

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(DISTINCT Posts.Id) --/ @Total

FROM
    Posts

    INNER JOIN
    Votes
    ON Posts.Id = Votes.PostId

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

GROUP BY
    PostTypeId

HAVING
    COUNT(*) > 20    

--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

fork query

created jan 6 12 ThinkingStiff