SELECT pu.OwnerUserId AS [User Link], pu.TotalCount, pu.P...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском

Вопросы и ответы для программистов

SELECT pu.OwnerUserId AS [User Link],
       pu.TotalCount,
       pu.PopularCount,
       pu.PopularCount*1.0/pu.TotalCount AS PopularRatio
FROM
  (SELECT p.OwnerUserId,
          count(1) AS TotalCount,
          sum(CASE
                  WHEN p.ViewCount>=1000 THEN 1
                  ELSE 0
              END) AS PopularCount
   FROM Posts p
        JOIN Users u on p.OwnerUserId = u.Id
   WHERE p.PostTypeId = 1 --Questions
         and u.LastAccessDate > DATEADD(year,-1,GETDATE())
         and exists (SELECT 1 
           FROM Posts QuestionsLastYear 
           WHERE QuestionsLastYear.OwnerUserId = u.Id 
               and QuestionsLastYear.PostTypeId = 1
               and QuestionsLastYear.ViewCount>=1000
               and QuestionsLastYear.CreationDate > DATEADD(year,-1,GETDATE())
         )
   GROUP BY p.OwnerUserId
   HAVING count(1) >= 10) pu
WHERE pu.PopularCount>0
ORDER BY PopularRatio DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results