0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people studying math at any level and professionals in related fields

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 * from prolific_user_cte
/*SELECT
	TOP (20) u.DisplayName AS 'user_name',
	u.Location AS 'user_location',
	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*/

fork query

edited dec 3

created dec 3 anonymous