SELECT TOP 5000 U2.Reputation, DAY(P3.CreationDate) AS "T...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

SELECT TOP 5000
    U2.Reputation,
    DAY(P3.CreationDate) AS "TimeOfDay",
    DATEDIFF("dd", U2.CreationDate, P3.CreationDate)
        AS "MembershipLength",
    ROUND((CAST(AR.AcceptedAnswers AS float) / AR.TotalQuestions * 100),1) AS AcceptanceRate,
    --DATEDIFF("mi", P3.CreationDate, P4.CreationDate) AS "TimeToAnswer",
    case
      when DATEDIFF("mi", P3.CreationDate, P4.CreationDate) < 60 then 0
      when DATEDIFF("mi", P3.CreationDate, P4.CreationDate) < 240 then 1
      when DATEDIFF("mi", P3.CreationDate, P4.CreationDate) < 720 then 2
      else 3
    end as "TimeToAnswerDiscretized"
    
FROM
    Posts P3
  INNER JOIN
    Users U2 ON U2.Id = P3.OwnerUserId
  INNER JOIN
  (
    SELECT
        U.Id AS UserId,
        COUNT(*) AS AcceptedAnswers,
        (   
            SELECT COUNT(*)
            FROM Posts P2
            WHERE
              U.Id = P2.OwnerUserId
              AND P2.PostTypeId = 1 
        ) AS TotalQuestions
    FROM
        Posts P1
      INNER JOIN
        Users U ON U.Id = P1.OwnerUserId
    
    WHERE 
        P1.PostTypeId = 1 
        AND P1.CommunityOwnedDate is null
        AND P1.ClosedDate is null
        AND P1.AcceptedAnswerId IS NOT NULL
    GROUP BY U.Id  
    ) AR ON P3.OwnerUserId = AR.UserId
    INNER JOIN
      Posts P4 ON P3.AcceptedAnswerId = P4.Id
  
WHERE 
    P3.PostTypeId = 1 
    and P3.CommunityOwnedDate is null
    and P3.ClosedDate is null
    and DATEDIFF("dd", U2.CreationDate, P3.CreationDate) >= 0
ORDER BY NEWID()

Enter Parameters

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