0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

SELECT
  RANK() OVER (ORDER BY SUM(
                          CASE 
                            WHEN P1.AcceptedAnswerID = P2.ID
                              THEN (P2.SCORE * 10) + 15
                              ELSE P2.SCORE * 10
                            END
                            ) DESC),
  U1.DisplayName,
  U1.Reputation AS 'Total Rep',
  SUM(
    CASE 
      WHEN P1.AcceptedAnswerID = P2.ID
        THEN (P2.SCORE * 10) + 15
        ELSE P2.SCORE * 10
      END
      ) 'Rep from SQL answers',
  U1.Age,
  ROUND(
      SUM(
        CASE 
          WHEN P1.AcceptedAnswerID = P2.ID
            THEN (P2.SCORE * 10.0) + 15
            ELSE P2.SCORE * 10.0
         END) * 100
       / U1.Reputation, 2) '% of total rep from SQL answers',
    U1.Location,
    CAST((DATEDIFF(SECOND, U1.CreationDate, GETDATE())/60/60/24/365) AS NVARCHAR) + 
      CASE 
        WHEN DATEDIFF(SECOND, U1.CreationDate, GETDATE())/60/60/24/365 <> 1 THEN ' Years '
        ELSE ' Year '
      END +
      + CAST((DATEDIFF(SECOND, U1.CreationDate, GETDATE())/60/60/24%365) AS NVARCHAR) + ' Days '
      + CAST((DATEDIFF(SECOND, U1.CreationDate, GETDATE())/60/60%24) AS NVARCHAR) + ' Hours '
      + CAST((DATEDIFF(SECOND, U1.CreationDate, GETDATE())/60%60) AS NVARCHAR) + ' Minutes '
      + CAST((DATEDIFF(SECOND, U1.CreationDate, GETDATE())%60) AS NVARCHAR) + ' Seconds ' AS 'Member For'
FROM POSTS P1
    INNER JOIN POSTS P2 ON P1.ID = P2.ParentID 
            AND P1.OWNERUSERID <> P2.OWNERUSERID
    INNER JOIN USERS U1 ON U1.ID = P2.OWNERUSERID
            AND U1.LOCATION = 'Cluj-Napoca, Romania'
WHERE 
  P1.PostTypeID = 1 
    AND UPPER(P1.TAGS) LIKE '%SQL%'
GROUP BY
  U1.DisplayName,
  U1.Location,
  U1.Reputation,
  U1.AboutMe,
  U1.CreationDate,
  U1.Age
Order BY 4 DESC

fork query

edited oct 27 13

created oct 27 13 anonymous