python_08_14_Oct_13

0

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans Meta

Q&A about the site for music historians, critics, and fans

WITH
tblQuestion (Id, OwnerUserId, UpVotes, DownVotes, Reputation) AS 
(
SELECT  a.Id,
        a.OwnerUserId,
        SUM(CASE WHEN b.VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
        SUM(CASE WHEN b.VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes,
        a.Reputation
FROM    (
        SELECT  a.*,
                e.Reputation
        FROM    Posts a,
                PostTypes b,
                PostTags c,
                Tags d,
                Users e
        WHERE   a.PostTypeId = b.Id
        AND     a.Id = c.PostId
        AND     c.TagId = d.Id
        AND     a.OwnerUserId = e.Id
        AND     b.Name = 'Question'
        AND     d.TagName IN ('python')
        AND     a.CreationDate BETWEEN '2013-10-08' AND '2013-10-14'
        ) a LEFT JOIN
        Votes b
        ON a.Id = b.PostId
GROUP BY a.Id, a.OwnerUserId, a.Reputation
),
tblAnswer (Id, ParentId, OwnerUserId, UpVotes, DownVotes, Reputation) AS 
(
SELECT  a.Id,
        a.ParentId,
        a.OwnerUserId,
        SUM(CASE WHEN b.VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
        SUM(CASE WHEN b.VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes,
        a.Reputation
FROM    (
        SELECT  a.*,
                f.Reputation
        FROM    Posts a,
                PostTypes b,
                PostTags c,
                Tags d,
                Posts e,
                Users f
        WHERE   a.PostTypeId = b.Id
        AND     a.ParentId = c.PostId
        AND     c.TagId = d.Id
        AND     e.Id = a.ParentId
        AND     a.OwnerUserId = f.Id
        AND     b.Name = 'Answer'
        AND     d.TagName IN ('python')
        AND     e.CreationDate BETWEEN '2013-10-08' AND '2013-10-14'
        ) a LEFT JOIN
        Votes b
        ON a.Id = b.PostId
GROUP BY a.Id, a.ParentId, a.OwnerUserId, a.Reputation
)
SELECT  a.QuestionId,
        a.SrcNodeId,
        a.DstNodeId,
        a.EdgeAttrUpVotes,
        a.EdgeAttrDownVotes,
        a.EdgeAttrSrcRep,
        a.EdgeAttrDstRep,
        b.Tags AS EdgeAttrTags
FROM    (
        SELECT  a.Id AS QuestionId,
                a.OwnerUserId AS SrcNodeId,
                b.OwnerUserId AS DstNodeId,
                b.UpVotes AS EdgeAttrUpVotes,
                b.DownVotes AS EdgeAttrDownVotes,
                a.Reputation AS EdgeAttrSrcRep,
                b.Reputation AS EdgeAttrDstRep
        FROM    tblQuestion a,
                tblAnswer b
        WHERE   a.Id = b.ParentId
        ) a,
        Posts b
WHERE   a.QuestionId = b.Id 
-- AND     a.QuestionId = 613183  -- DEBUG 1
-- AND     a.DstNodeId = 2786  -- DEBUG 2
ORDER BY a.SrcNodeId, a.DstNodeId;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results