3

Please login or register to vote for this query.

(click on this box to dismiss)

An abnormally short interval between the time a question and answer are posted may be an indicator of collusion between users, especially if the question and answer are long. This query attempts to uncover such patterns of collusion. "Gun speed" is the length of the question-and-answer pair divided by response time, with the answer length weighted 9 times as much as the question length. Take results with a grain of salt. Before making accusations, take other factors into consideration. Keep in mind that there is an editing grace period that is unaccounted for here. Ideally, we should strip out the longest common substrings between the question and answer when considering the answer length.

Q&A for those who study, teach, research and apply economics and econometrics

WITH QA AS (
    SELECT A.Id AS AId
         , A.OwnerUserId AS AUserId
         , Q.OwnerUserId AS QUserId
         , Q.Score AS QScore
         , A.Score AS AScore
         , LEN(Q.Body) AS QLen
         , LEN(A1.Text) AS ALen
         , DATEDIFF(s, Q.CreationDate, A.CreationDate) AS Seconds
         , (0.0 + LEN(Q.Body) + 9 * LEN(A1.Text)) / (0.00001 + DATEDIFF(s, Q.CreationDate, A.CreationDate)) / 10.0 AS GunSpeed
        FROM Posts AS Q
            INNER JOIN Posts AS A
                ON Q.PostTypeId = 1
                AND A.PostTypeId = 2
                AND Q.Id = A.ParentId
                AND Q.OwnerUserId <> A.OwnerUserId              -- Exclude selfies
            INNER JOIN PostHistory AS A1
                ON A.Id = A1.PostId
                AND A.CreationDate = A1.CreationDate
        WHERE Q.ClosedDate IS NULL
            AND NOT EXISTS (
                SELECT Id
                    FROM PostHistory
                    WHERE PostId = Q.ID
                        AND PostHistoryTypeId BETWEEN 35 AND 38 -- Exclude migrations and merges
            )
), CollusionStats AS (
    SELECT *
         , COUNT(*) OVER (PARTITION BY QUserId) AS QCollusionCount
         , COUNT(*) OVER (PARTITION BY AUserId) AS ACollusionCount
         , AVG(GunSpeed) OVER (PARTITION BY QUserId) AS QGunSpeed
         , AVG(GunSpeed) OVER (PARTITION BY AUserId) AS AGunSpeed
        FROM QA
        WHERE
            Seconds < ##ResponseTimeLimitSecs:int?300##
            AND GunSpeed > ##GunSpeedThreshold:float?3.0##
), CollusionAggregates AS (
    SELECT *
         , CASE WHEN QCollusionCount > ACollusionCount THEN QCollusionCount
                ELSE ACollusionCount
           END AS CollusionCount
         , CASE WHEN QGunSpeed > AGunSpeed THEN QGunSpeed
                ELSE AGunSpeed
           END AS MaxGunSpeed
        FROM CollusionStats
)
SELECT AId AS [Post Link]
     , QUserId AS [User Link]
     , CONCAT('site://users/', CAST(AUserId AS VARCHAR(10)) COLLATE SQL_Latin1_General_CP1_CI_AS, '|', Users.DisplayName COLLATE SQL_Latin1_General_CP1_CI_AS) AS [Answerer]
     , Seconds
     , QLen AS [Q Len]
     , ALen AS [A Len]
     , GunSpeed AS [Gun Speed]
     , CollusionCount
     , AScore AS [A Score]
    FROM CollusionAggregates
        LEFT OUTER JOIN Users
            ON AUserId = Users.Id
    ORDER BY CollusionCount * MaxGunSpeed DESC;

fork query

edited oct 21 14

created oct 20 14 200_success