Users sorted by total number of views of their questions per day (with a 30-day softener to keep hot new posts from skewing the results).
Q&A for database professionals who wish to improve their database skills and learn from others in the community
-- Who Brings in the Crowds?
-- Users sorted by total number of views of their questions per day (softened by 30 days to keep new hot posts from skewing the results)
-- I tried removing the softener, but the results are really more useful with it
-- updated to use last database access (by a logged in user -- best we've got) instead of current_timestamp
SELECT TOP 50
q.OwnerUserId as [User Link],
count(q.Id) as Questions,
sum(q.ViewCount/(30+datediff(day, q.CreationDate, datadumptime ))) AS [Question Views per Day]
FROM posts AS q, (select max(LastAccessDate) as datadumptime from Users) tmp
q.CommunityOwnedDate is null AND
q.OwnerUserId is NOT null AND
GROUP BY q.OwnerUserId
ORDER BY [Question Views per Day] DESC