0

Please login or register to vote for this query.

(click on this box to dismiss)

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)
    from posts 
    where owneruserid in (select id from a1_users)
  ) as t0 
  left join users t1
  on t0.owneruserid = t1.id
  
) as joined
group by id
*/


    select *,
    rank() over (Partition by owneruserid Order by favoritecount desc)
    from posts 
    where owneruserid in (select id from @a1_users)

fork query

edited oct 16 17

created oct 13 17 anonymous