QUESTION E

0

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

--A list of showing the best users (showing user display name) of only posts 
--without a title and users with less than 8 answers. 
--Show top 5 with highest average answer score

SELECT TOP 5 pt.OwnerDisplayName
		,AVG(pt.Score) as Avg_Score 
FROM (SELECT *
		FROM(
		SELECT p.OwnerUserId
			  ,p.OwnerDisplayName
			  ,ROW_NUMBER() OVER(PARTITION BY p.OwnerUserId ORDER BY p.Id) as row_num
			FROM Posts p
			) p 
            GROUP BY p.OwnerUserId
			  ,p.OwnerDisplayName
                  ,p.row_num
	HAVING MAX(row_num) < 8
	)a LEFT JOIN Posts pt ON a.OwnerUserId = pt.OwnerUserId 
			AND a.OwnerDisplayName = pt.OwnerDisplayName
WHERE pt.Title IS NULL
GROUP BY pt.OwnerDisplayName
ORDER BY AVG(pt.Score) DESC



SELECT *
FROM Posts p
WHERE p.OwnerDisplayName = 'blivet'
    --  AND p.Title IS NULL

Enter Parameters

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