3

Please login or register to vote for this query.

(click on this box to dismiss)

Looks for posts with close to 10, 25, 100 total score OR favorited by close to 25 people OR answers close to 40 (for guru), etc. Ordered by a mix of scores

Q&A for those who study, teach, research and apply economics and econometrics

-- Spreading the badges : Posts close to be nice, good, great, etc. for low rep users
-- Looks for posts with close to 10, 25, 100 total score
-- OR favorited by close to 25 people
-- OR answers close to 40 (for guru), etc.
-- Ordered by a mix of scores

-- DONE : Join with the parent to check if the question is closed
-- DONE : Join with the parent to have the info for AcceptedAnswerId for Guru badge 
-- DONE : Join with the user to select users with less than 3000
-- DONE : See if one can select for Self-Learner
-- DONE : See if one can select for Reversal
-- TO DO : See if one can select for Enlightened

SET
  nocount off
SELECT
  top 500 p.id as [Post Link] ,
  p.Score as score,
  oq.Score as [Qsc],
  p.FavoriteCount as [fav],
  CASE WHEN p.ID = oQ.AcceptedAnswerID THEN 'Yes' ELSE '' END as ACC,
  CASE WHEN p.OwnerUserID = oQ.OwnerUserID THEN 'Yes' ELSE '' END as SELF,
  IsNULL(p.ViewCount, oq.ViewCount) as [views],
  p.Score+IsNULL(p.FavoriteCount,0)+round(IsNULL(p.ViewCount, IsNULL(oq.ViewCount,0))/100,0) as rem ,
  DATEDIFF(DAY, p.LastActivityDate,GETDATE()) as [sleep],
  DATEDIFF(DAY, oq.LastActivityDate,GETDATE()) as [Q sleep],
--  IsNULL(CONVERT(VARCHAR(8), p.ID),'---') as [Id],
--  IsNULL(CONVERT(VARCHAR(8), p.ParentID), '---') as parent,
--  IsNULL(CONVERT(VARCHAR(8), oq.AcceptedAnswerID),'---') as Accepted,
--  CONVERT(VARCHAR(12), p.LastActivityDate, 103) as [last],
--  CONVERT(VARCHAR(12), us.LastAccessDate, 103) as [user last],
  DATEDIFF(DAY, us.LastAccessDate,GETDATE()) as [U sleep],
  us.Reputation as [U rep]
FROM
  Posts as p
  LEFT OUTER JOIN
  Posts as oq
  ON p.ParentId = oq.Id
  LEFT JOIN
  Users as us
  ON p.OwnerUserId = us.Id 
WHERE
-- For S.O.  Do not include huge crowd attractors
   IsNULL(p.ViewCount, oq.ViewCount) < 3000
-- For Stimulation of small users
   AND us.Reputation < 3000
AND ( 
  (p.ViewCount >2450 AND p.ViewCount < 2500)
  OR (p.ViewCount >980 AND p.ViewCount < 1000)
  OR (p.Score = 9)
  OR (p.Score > 22 AND p.Score < 25)
  OR (p.ParentId IS NOT null AND p.Score > 35 AND p.Score < 40 AND oq.AcceptedAnswerId = p.Id)
  OR (p.Score > 90 AND p.Score < 100)
  OR (p.FavoriteCount>22 AND p.FavoriteCount<25) 
  OR (oq.Score <= -5 AND (p.Score > 17 AND p.Score < 20))
  OR (p.OwnerUserId = oq.OwnerUserId AND (p.Score > 1 and p.score <3))
) AND
  p.CommunityOwnedDate IS null AND p.ClosedDate IS null
AND
  oq.CommunityOwnedDate IS null AND oq.ClosedDate IS null
ORDER BY
  rem desc,
  p.FavoriteCount desc,
  p.ViewCount desc,
  age desc

fork query

created may 14 11 ogerard