acceptance rate for each user

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

SELECT 
    U2.Reputation,
    DATEDIFF("dd", U2.CreationDate, P3.CreationDate)
        AS "MembershipLength",
    (CAST(AR.AcceptedAnswers AS float) / AR.TotalQuestions * 100) AS AcceptanceRate,
    case
      when P3.AcceptedAnswerId is null then -1
      when P3.AcceptedAnswerId is not null then 1
    end as "AcceptedAnswers"
      
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
  
WHERE 
    P3.PostTypeId = 1
    and P3.CommunityOwnedDate is null
    and P3.ClosedDate is null
    and DATEDIFF("dd", U2.CreationDate, P3.CreationDate) >= 0

Enter Parameters

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