USE plwiki_p;

​

SET @row_num = 0; 

SET @row_num2 = 0; 

​

SELECT (@row_num2:= @row_num2 + 1) as number, actor_name, review_count, review_count_i

FROM

(

  SELECT (@row_num:= @row_num + 1) as number, actor_name, review_count

  FROM

  (

      SELECT count(*) as review_count, log_actor

      FROM logging 

      WHERE log_type = 'review' AND log_action = 'approve'

      AND log_timestamp >= 20190101000000

      AND log_timestamp <= 20191231235959

      GROUP BY log_actor

  ) r_next

  LEFT JOIN actor a ON actor_id = log_actor

  ORDER BY review_count DESC

) top600

  LEFT JOIN

  (

    SELECT count(*) as review_count_i, log_actor as log_actor_i

    FROM logging 

    WHERE log_type = 'review' AND log_action = 'approve-i'

    AND log_timestamp >= 20190101000000

    AND log_timestamp <= 20191231235959

    GROUP BY log_actor

  ) r_initial ON log_actor_i = log_actor

ORDER BY review_count DESC, review_count_i DESC