0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers and students of the Korean language

SET NOCOUNT ON

DECLARE @VoteStats TABLE (PostId int, up int, down int)

INSERT @VoteStats
SELECT
    v.PostId,
    up = SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END),
    down = SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END)
FROM Votes v
WHERE v.PostId BETWEEN 30000000 AND 32500000 AND v.VoteTypeId IN (2, 3)
GROUP BY v.PostId

SET NOCOUNT OFF

SELECT p.id, p.body, p.title, p.creationdate, p.tags, vs.up,
       vs.down, u.Id as UserId, u.Location, u.DisplayName, u.aboutme, u.reputation
FROM @VoteStats vs
INNER JOIN Posts p ON vs.PostId = p.Id
INNER JOIN Users u ON p.OwnerUserId = u.Id
WHERE vs.up < 1  AND p.CommunityOwnedDate IS NULL 
      AND p.ClosedDate IS NULL 
      
      AND (
          u.Location LIKE '%United States%' OR
          u.Location LIKE '%India%' OR
          u.Location LIKE '%United Kingdom%' OR
          u.Location LIKE '%Germany%' OR
          u.Location LIKE '%Canada%' OR
          u.Location LIKE '%France%' OR
          u.Location LIKE '%Brazil%' OR
          u.Location LIKE '%Russia%' OR
          u.Location LIKE '%Australia%' OR
          u.Location LIKE '%Italy%'
        )




    AND p.body NOT LIKE '%<code>%'
    AND u.Reputation <= (
        SELECT TOP 1 PERCENTILE_CONT(0.98) WITHIN GROUP (ORDER BY Reputation) OVER () AS Bottom_50_Percent_Reputation
        FROM Users
    )

fork query

edited may 22

created may 22 anonymous