Skip to main content
fix grammar issue
Source Link
Simon Forsberg
  • 59.4k
  • 9
  • 156
  • 309

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, it'sits 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

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, it's 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

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
Tweeted twitter.com/StackCodeReview/status/799233879064383488
Source Link
Peilonrayz
  • 43.5k
  • 7
  • 76
  • 155

Where are my downvotes?

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, it's 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