0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and power users of the Android operating system

-- Talent Search Based on Location and Tags
--
-- How to make it faster? Temporary table on Users (by Location) or Posts (by Tags) seems too big.

SELECT
  ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [No], 
  Id AS [User Link], 
  Location, 
  QuestionScore AS [Question Score], 
  AnswerScore AS [Answer Score], 
  Reputation
FROM
  Users FULL OUTER JOIN
  (
    SELECT
      OwnerUserId, 
      SUM(Score) AS QuestionScore
    FROM
      Posts
    WHERE
      OwnerUserId IN
      (
        SELECT
          Id
        FROM
          Users
        WHERE
          LOWER(Location) LIKE '%##Location##%' OR
          UPPER(Location) LIKE '%##Location##%'
      ) AND
      ParentId IS NULL AND
      (
        LOWER(Tags) LIKE '%##Tags##%' OR
        UPPER(Tags) LIKE '%##Tags##%'
      )
    GROUP BY OwnerUserId
  ) AS QuestionScore
  ON Users.Id = QuestionScore.OwnerUserId FULL OUTER JOIN
  (
    SELECT
      OwnerUserId, 
      SUM(Score) AS AnswerScore
    FROM
      Posts
    WHERE
      OwnerUserId IN
      (
        SELECT
          Id
        FROM
          Users
        WHERE
          LOWER(Location) LIKE '%##Location##%' OR
          UPPER(Location) LIKE '%##Location##%'
      ) AND
      ParentId IS NOT NULL AND
      ParentId IN
      (
        SELECT
          Id
        FROM
          Posts
        WHERE
          ParentId IS NULL AND
          (
            LOWER(Tags) LIKE '%##Tags##%' OR
            UPPER(Tags) LIKE '%##Tags##%'
          )
      )
    GROUP BY OwnerUserId
  ) AS AnswerScore
  ON Users.Id = AnswerScore.OwnerUserId
WHERE
  (
    LOWER(Location) LIKE '%##Location##%' OR
    UPPER(Location) LIKE '%##Location##%'
  ) AND
  (
    QuestionScore IS NOT NULL OR
    AnswerScore IS NOT NULL
  )
ORDER BY
  Reputation DESC

fork query

edited aug 6

created aug 6 SridharKritha