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 anime and manga fans
-- 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