0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the French language

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
declare @total_favorites table(owneruserid int, total_favorites float)
insert @total_favorites
select owneruserid, sum(favoritecount) as total_favorites
from @userposts
group by owneruserid
  
 --select * from @total_favorites

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 @total_favorites 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*/

fork query

edited oct 16 17

created oct 13 17 anonymous