0

Please login or register to vote for this query.

(click on this box to dismiss)

This allows you to search all the answers posted to your questions or to the questions of any other user (you will need their UserId).

Q&A for people using the vi and Vim families of text editors

/*
Use Search Parameters as follows :

- SearchTerm : Enter terms that you want to search for. Only limitation is that an exact search is performed so be careful when searching for multiple terms in a single query.  

NOTE :: If you do not want to search for a specific term, then fill this field a '%', without quotes 

- MinScore : The minimum score of the posts that you would like to search in.
- MaxScore : The maximum score of the posts that you would like to search in.
- IsAccepted : Set this to 1 when searching for only *accepted answers* to your questions. When searching for all answers, set it to 0 (default value).
- QuestionTags : This is an optional field. Leave it as is, if you do not need it. Otherwise, add search tags into this field. Again, since an exact match is performed, be careful of order and case when searching for multiple tags.
- SelfAnswer : Set this to 1 when searching for *your answers* to your questions. When searching for all answers, set it to 0 (default value).
*/
with c as (
SELECT
        P2.Id AS [Post Link], 
        P1.Score as AnswerScore,
        (SELECT AVG (CAST (Score AS FLOAT)) FROM Posts WHERE Posts.PostTypeId =2) AS OverallAnswerAverage,
        (SELECT AVG (CAST (Score AS FLOAT)) FROM Posts WHERE Posts.PostTypeId =1) AS OverallQuestionAverage,
        (SELECT AVG (CAST (ViewCount AS FLOAT)) FROM Posts WHERE Posts.PostTypeId =1) AS OverallViewAverage,
        (SELECT AVG (CAST (FavoriteCount AS FLOAT)) FROM Posts WHERE Posts.PostTypeId =1) AS OverallFavoriteCountAverage,
        P2.Score as QuestionScore,
        p2.ViewCount,
        p2.FavoriteCount,
        p2.OwnerUserID AS UserLink,
        P1.OwnerUserID AS [User Link]
       
FROM Posts as P1, Posts as P2
WHERE P2.Id = P1.ParentId and --Join of Questions with Answers--
      P2.PostTypeId = 1 and -- P2 Contains Questions --
      P1.PostTypeId = 2  -- P1 contains Answers --
      
      AND P2.OwnerUserID = P1.OwnerUserID
      AND   CAST (P2.id AS INT) +1 =  CAST (P1.id AS INT)

      
      
      )
      

      SELECT DISTINCT USerLink AS [User Link],
      Reputation,
      UpVOTes + Downvotes AS TotalVotes
      
      
      /*
      OverallAnswerAverage,
      OverallQuestionAverage,
      OverallViewAverage,
	  OverallFavoriteCountAverage,
     AVG(CAST(ViewCount AS FLOAT)) AS SelfAnswerViewAverage ,
     AVG(CAST(AnswerScore AS FLOAT)) AS SelfAnswerScoreAverage,
     AVG(CAST(QuestionScore AS FLOAT)) AS SelfQuestionScoreAverage,
     AVG(CAST(FavoriteCount AS FLOAT)) AS SelfAnswerFavoriteCountAverage
    */
      FROM C
      
      JOIN Users ON Users.Id = UserLink
     -- GROUP BY OverallAnswerAverage,OverallQuestionAverage,OverallViewAverage,OverallFavoriteCountAverage

fork query

edited jan 18 18 anonymous

created aug 18 13 AsheeshR