0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people interested in the history and origins of science and mathematics

WITH aggregated_votes AS (
    SELECT 
        PostId, 
        COUNT(CASE WHEN VoteTypeId = 2 THEN 1 END) AS positive_votes,
        COUNT(CASE WHEN VoteTypeId = 3 THEN 1 END) AS negative_votes
    FROM 
        Votes
    WHERE 
        VoteTypeId IN (2, 3)
    GROUP BY 
        PostId
),
ranked_posts AS (
    SELECT 
        PostId, 
        positive_votes,
        negative_votes,
        COALESCE(positive_votes, 0) - COALESCE(negative_votes, 0) AS diff_of_votes,
        ROW_NUMBER() OVER (ORDER BY COALESCE(positive_votes, 0) - COALESCE(negative_votes, 0) DESC) AS descending_valuation,
        ROW_NUMBER() OVER (ORDER BY COALESCE(positive_votes, 0) - COALESCE(negative_votes, 0) ASC) AS ascending_valuation
    FROM 
        aggregated_votes
)
SELECT 
    STRING_AGG(PostId, ', ') AS list_of_PostId
FROM 
    ranked_posts 
WHERE 
    descending_valuation <= 10 
    OR ascending_valuation <= 10;

fork query

created nov 24 jon.doe46369