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
    Posts.Id AS [Post Link],
    SUM( CASE WHEN Votes.VoteTypeId = 2 THEN 1 ELSE 0 END ) AS Up,
    SUM( CASE WHEN Votes.VoteTypeId = 3 THEN 1 ELSE 0 END ) AS Down,
    ( SUM( CASE WHEN Votes.VoteTypeId = 2 THEN 1 ELSE 0 END )
        - SUM( CASE WHEN Votes.VoteTypeId = 3 THEN 1 ELSE 0 END ) )
        * 5 AS Score

FROM
    Posts

    INNER JOIN
    Votes
    ON Posts.Id = Votes.PostId

WHERE
        --Score              > 100 --great
    PostTypeId         = 1 --question
    AND CommunityOwnedDate IS NULL
    AND Votes.VoteTypeId   IN ( 2, 3 ) --up, down
    AND OwnerUserId IS NOT NULL

GROUP BY
    Posts.Id

HAVING
    ( SUM( CASE WHEN Votes.VoteTypeId = 2 THEN 1 ELSE 0 END )
        - SUM( CASE WHEN Votes.VoteTypeId = 3 THEN 1 ELSE 0 END ) )
        * 5 > 100 --great question    

ORDER BY
    Score DESC

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