USE plwiki_p;

​

SET @row_num = 0; 

SET @row_num2 = 0; 

SET @row_num3 = 0; 

​

SELECT *

FROM

(

​

SELECT actor_name, review_count

, IFNULL(review_count_i, 0) as review_count_intial

, review_count + IFNULL(review_count_i, 0) as review_count_total

, cast(review_count as double) * 100000.0 + cast(IFNULL(review_count_i, 0) as double) as review_count_power

FROM

-- next reviews

(

  SELECT actor_name, review_count, log_actor

  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

) top_r_next

-- add initial reviews

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

​

) as blaaaaa