select * from ( select *, dense_rank() over ( order by t...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

--A monthly “Prolific User” is defined as a user 
--who has an average score higher than 1.5 and at least 3 posts. 
--Rank September 2021 users by their average score divided by 
--the overall Prolific users average score and show the top 20 users
--by their Display name, their location and the metrics.

select * from
(
select *,
dense_rank() over (
order by t.score_for_rank desc
) as rank_id
from(
select OwnerUserId , u.Location , u.DisplayName ,
avg(cast(Score as float)) as average_score,
avg(avg(cast(Score as float))) over() as overall_prolific_users_average_score,
avg(cast(Score as float))/avg(avg(cast(Score as float))) over() as score_for_rank
from Posts p inner join Users u
on p.OwnerUserId = u.Id
where year(p.CreationDate)= 2021
and month(p.CreationDate)= 9
group by OwnerUserId ,  u.Location , u.DisplayName
having avg(Score) > 1.5 and count(p.Id) > 3) as t
) as t2
where rank_id<=20

Enter Parameters

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