18
\$\begingroup\$

I've been thinking my posts aren't that great, and so I've been wanting to improve them. One sure fire way to do this is to look at all of my downvoted posts. Not knowing if Stack Exchange allows you to do this already, I decided I should learn SQL, and wrote a SEDE query. The query, downvoted posts by user, takes a single parameter which is the user id. And returns a list of downvoted posts, with a link to the post, its score, and the amount of downvotes.

I've never written a line of SQL before, and so I'm unsure if my query is any good. I think using GROUP BY is a bit weird to get a sum of all the downvotes, is there a better way to do this? And having looked at various other SEDE queries, I don't know if my style is any good either.

declare @user_id int = ##UserId:int##

SELECT
    p.Id as [Post Link],
    p.Score,
    sum(case when p.Id = v.PostId then 1 else 0 end) as [Downvotes]
FROM
    Posts p
inner join
    Votes v on p.Id = v.PostId
WHERE
    p.OwnerUserId = @user_id
and v.VoteTypeId=3
GROUP BY
    p.Id, p.Score
ORDER BY 'Downvotes' DESC, Score DESC
\$\endgroup\$
  • \$\begingroup\$ FWIW you can do this already by going to your own user profile and sorting your answers by score. \$\endgroup\$ – Peter Taylor Nov 17 '16 at 14:35
  • \$\begingroup\$ @PeterTaylor Do you mean like this? As that doesn't do the same as the above query. \$\endgroup\$ – Peilonrayz Nov 17 '16 at 14:44
20
\$\begingroup\$

If this is actually your first SQL you're quite good in learning new languages.

Some remarks:

The Group By is not weird, it's the SUM, you apply a conditional aggregation but the result is the same as a simple COUNT(*) (you can always get the same result using different ways in SQL).

The ORDER BY 'Downvotes' DESC will not work as expected, because single quotes specify strings in SQL, so this is not sorting by the actual number of downvotes, but by the string 'Downvotes' (which of course is the same for all rows). In your case you can simply use Downvotes, [] ([Downvotes]) or better Standard SQL double quotes ("Downvotes") are only needed for names including spaces or non-standard characters, like "foo bar?"

declare @user_id int = ##UserId:int##

SELECT
    p.Id as "Post Link",
    p.Score,
    count(*) as Downvotes
FROM
    Posts p
inner join
    Votes v on p.Id = v.PostId
WHERE
    p.OwnerUserId = @user_id
and v.VoteTypeId=3
GROUP BY
    p.Id, p.Score
ORDER BY Downvotes DESC, Score DESC
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.