0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

DECLARE @UserId int = ##UserId##

SELECT 
    P3.Id As [Post Link],
    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 not null then P3.AcceptedAnswerId
    end as "AcceptedAnswerId"
      
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 
    U2.Id = @UserId
    and P3.PostTypeId = 1
    and P3.CommunityOwnedDate is null
    and P3.ClosedDate is null
    order by acceptancerate asc

fork query

edited may 6 21 tony1934261

created apr 11 12 Petr Praus