I was trying to track my performance here in SO using StackExchange - Query Stack Overflow and came up with this query:
At first I have this one to get the list of my answered questions:
Select Distinct a.Id, a.CreationDate, u.DisplayName, p.Title, p.Tags,
CONCAT('http://stackoverflow.com/questions/', p.Id, '#answer-', a.Id) as Link
From Posts a
Inner Join Posts p On a.ParentId = p.Id
Inner Join Users u On a.OwnerUserId = u.Id
Inner Join PostTags pt on p.Id = Pt.PostId
Inner Join Tags t on pt.TagId = t.Id
Where a.OwnerUserId in (
9461114 --Me
)
And a.PostTypeId = 2 -- Answer
And p.PostTypeId = 1 -- Question
And t.TagName in ('jquery')
Then I did another query to get all the answers raw score (Upvote, Downvote, Accepted)
SELECT *
FROM (
SELECT v.PostID as Id, vt.Name, COUNT(*) AS CNT
FROM
Posts p
INNER JOIN Votes v
ON v.PostId = p.Id
INNER JOIN VoteTypes vt
ON v.VoteTypeId = vt.Id
WHERE
p.OwnerUserId in
(
9461114 --Me
)
GROUP BY
vt.Name, v.PostID) s_tab pivot (min(CNT)for [Name] in ([UpMod], [DownMod],[AcceptedByOriginator]))AS PVT
Then to make things easier, I used LEFT JOIN
to merge the two queries that I have, and here's what I came up with:
Select a.Id, a.CreationDate, a.DisplayName, a.Title, a.Tags, a.Link, b.UpMod as Upvote, b.DownMod as DownVote, b.AcceptedByOriginator as Accepted
From
(Select Distinct a.Id, a.CreationDate, u.DisplayName, p.Title, p.Tags,
CONCAT('http://stackoverflow.com/questions/', p.Id, '#answer-', a.Id) as Link
From Posts a
Inner Join Posts p On a.ParentId = p.Id
Inner Join Users u On a.OwnerUserId = u.Id
Inner Join PostTags pt on p.Id = Pt.PostId
Inner Join Tags t on pt.TagId = t.Id
Where a.OwnerUserId in (
9461114 --Me
)
And a.PostTypeId = 2 -- Answer
And p.PostTypeId = 1 -- Question
And t.TagName in ('jquery')
) a
Left Join
(SELECT *
FROM (
SELECT v.PostID as Id, vt.Name, COUNT(*) AS CNT
FROM
Posts p
INNER JOIN Votes v
ON v.PostId = p.Id
INNER JOIN VoteTypes vt
ON v.VoteTypeId = vt.Id
WHERE
p.OwnerUserId in
(
9461114 --Me
)
GROUP BY
vt.Name, v.PostID) s_tab pivot (min(CNT)for [Name] in ([UpMod], [DownMod],[AcceptedByOriginator]))AS PVT
) b on a.Id = b.Id
This is too long and I feel like there are some other ways to do this without making the query look like this.
I have tried and tested my query and am quite sure that it works just how I wanted it to. I just want it to be shorter. Thanks in advance!
You want to see your answers on jquery questions.
In your query you are joining tags, thus having to account for multiple rows per answer, although the posts have a redundant tags string. You are doing this in order to limit your results to jquery questions, but by joining and possibly multiplying rows, you need
DISTINCT
later to get rid of what you produced. This is not considered good style. What you want is to look up tags. We look up things in theWHERE
clause withEXISTS
orIN
.As to getting and counting votes the most elegant way should be an
OUTER APPLY
- or even aCROSS APPLY
as we are talking about aggregates - on your answers.This is not the shortest query we can write for the task, but it's probably the most performant way to approach it. I also consider this query very readable and thus maintainable, because you can easily see for instance why we check PostTags and Tags.