WITH prolific_user_cte AS( SELECT u.Id as 'user_id', cou...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

WITH prolific_user_cte AS( 

SELECT u.Id as 'user_id', count(p.Id) 'number_posts', 
      ROUND(AVG(CAST(p.Score AS FLOAT)), 2) 'avg_score_per_user'
		FROM
			Users u
	INNER JOIN Posts p ON p.OwnerUserId = u.Id
    GROUP BY u.Id
    HAVING AVG(CAST(p.Score AS FLOAT)) > 1.5 and COUNT(p.Id) >3 
    
)

SELECT TOP(20) u.DisplayName as'user_name', u.Location as 'user_location', AVG(CAST(p.Score AS FLOAT)) user_avg,
AVG(CAST(p.Score AS FLOAT))/(select AVG(avg_score_per_user) from prolific_user_cte) as score 
		FROM
			Users u
	INNER JOIN Posts p ON p.OwnerUserId = u.Id
    GROUP BY u.DisplayName, u.Location, p.CreationDate
    HAVING YEAR(p.CreationDate) = 2021 AND MONTH(p.CreationDate) = 09
    ORDER BY score DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results