Q&A about the site for electronic hardware hacking enthusiasts
declare @a1_users table (owneruserid int) insert @a1_users select distinct id from ( select t1.*, t0.tags from posts t0 left join users t1 on t0.owneruserid = t1.id WHERE tags like '%bigdata%' or tags like '%database%' ) as joined where year(lastaccessdate) >= 2017 and reputation > 400000 declare @userposts table(owneruserid int, favoritecount float, rank int) insert @userposts select owneruserid as id, cast(favoritecount as float) as favoritecount, rank() over (Partition by owneruserid Order by favoritecount desc) as rank from posts where owneruserid in (select owneruserid from @a1_users) --select * from @userposts select owneruserid, sum(favoritecount) as total_favorites from @userposts group by owneruserid /*declare @userposts_with_total table(owneruserid int, favoritecount float, rank int, total_favorites float) insert @userposts_with_total select t0.*, t1.total_favorites from @userposts t0 left join ( select owneruserid, sum(favoritecount) as total_favorites from @userposts group by owneruserid ) t1 on t0.owneruserid = t1.owneruserid select * from @userposts_with_total select owneruserid, sum(case when rank <= 3 then favoritecount else 0 end)/avg(total_favorites) as posts1_3, sum(case when rank <= 10 and rank >= 4 then favoritecount else 0 end)/avg(total_favorites) as posts4_10, sum(case when rank > 10 then favoritecount else 0 end)/avg(total_favorites) as posts11_inf from @userposts_with_total group by owneruserid*/