SockPuppetOrNot

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

-- For a given user, show me the number of questions they have marked answered 
-- for another user,
-- where they have at least 2 answers

SELECT
    Q.QuestionUserId
--,   Q.AttemptedAnswerUserId
,   SUM(Q.AcceptedAnswerCount) AS AcceptedAnswerCount
,   SUM(Q.AnswerNotAcceptedCount) AS AnswerNotAcceptedCount
,   SUM(Q.AcceptedAnswerCount + Q.AnswerNotAcceptedCount) AS SubmittedAnswerCount
,   FORMAT(SUM(Q.AcceptedAnswerCount) / (1. * SUM(Q.AcceptedAnswerCount + Q.AnswerNotAcceptedCount)), 'P') SuccessRate
FROM
(


    SELECT
        -- Who asked the question
        Q.OwnerUserId AS QuestionUserId
    ,   A.OwnerUserId AS AttemptedAnswerUserId
    ,   CASE WHEN Q.AcceptedAnswerId = A.Id THEN 1
            ELSE 0
        END AS AcceptedAnswerCount
    ,   CASE WHEN Q.AcceptedAnswerId <> A.Id THEN 1
            WHEN Q.AcceptedAnswerId IS NULL THEN 1
            ELSE 0
        END AS AnswerNotAcceptedCount
    -- How long did it take for the accepted answer to arrive?
    ,   CASE WHEN Q.AcceptedAnswerId = A.Id THEN DATEDIFF(minute, Q.CreationDate, A.CreationDate)
        ELSE 0
    END AS TimeBeforeAnswer_minutes

--,   DATEDIFF(minutes, Q.CreationDate, A.CreationDate
    --,*
    FROM 
    
        dbo.Posts AS Q
        INNER JOIN
            -- Who tried to answer
            dbo.Posts AS A
            ON A.ParentId = Q.Id
    WHERE
        Q.PostTypeId = 1
        AND A.PostTypeId = 2
        AND A.OwnerUserId = 9517769
        
        and Q.OwnerUserId = 15012638 -- suspected sock puppet
        -- SEDE does not have 65904631 in it as that question was just created yesterday 2021-01-26
        

)Q
WHERE
    Q.AttemptedAnswerUserId = 9517769
    --and Q.QuestionUserId = 2447136 -- WHy is this 0 counts
GROUP BY
    Q.QuestionUserId
,   Q.AttemptedAnswerUserId
HAVING
    SUM(Q.AcceptedAnswerCount) > 1
ORDER BY 5 desc

/*
SELECT
    Q.*
,   A.*
FROM
    dbo.Posts AS A
    OUTER APPLY
    (
    SELECT
        -- Who asked the question
        Q.OwnerUserId
    ,   Q.OwnerDisplayName
    ,   CASE WHEN Q.AcceptedAnswerId = A.Id THEN 1
            ELSE 0
        END AS AcceptedAnswerCount
    ,   CASE WHEN Q.AcceptedAnswerId <> A.Id THEN 1
            ELSE 0
        END AS AnswerNotAcceptedCount
    FROM 
        dbo.Posts AS Q
    WHERE
        Q.Id = A.ParentId

    )Q
WHERE
    A.OwnerUserId = 9517769 -- User in question 
    AND A.PostTypeId = 2 -- Answer 
    -- AND A.id = 65765113
ORDER BY
    A.AcceptedAnswerId desc;
    
*/

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results