Q&A for Joomla! administrators, users, developers and designers
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 /* select id, avg(case when rank <= 3 then favoritecount else 0) as posts1_3, avg(case when rank <= 10 and rank >= 4 then favoritecount else 0) as posts4_10, avg(case when rank > 10 then favoritecount else 0) as posts11_inf from ( select t1.*, t0.favoritecount, t0.rank from ( select *, rank() over (Partition by owneruserid Order by favoritecount desc) as rank from posts where owneruserid in (select owneruserid from @a1_users) ) as t0 left join users t1 on t0.owneruserid = t1.id ) as joined group by id */ --select * from @a1_users select t1.*, t0.favoritecount, t0.rank from ( select *, rank() over (Partition by owneruserid Order by favoritecount desc) as rank from posts where owneruserid in (select owneruserid from @a1_users) ) as t0 left join users t1 on t0.owneruserid = t1.id