Untitled query #45533

SQL

use arwiki_p;
select ori.*, r1.rev_timestamp latest_revision, r1.rev_len latest_size,
DATEDIFF(str_to_date(r1.rev_timestamp, '%Y%m%d'), str_to_date(created_when, '%Y%m%d')) growth_period,
ROUND(100 * (r1.rev_len / original_size - 1)) growth_percentage,
30 * ROUND(100 * (r1.rev_len / original_size - 1)) / DATEDIFF(str_to_date(r1.rev_timestamp, '%Y%m%d'), str_to_date(created_when, '%Y%m%d')) growth_rate 
from
(
  select page_title, rev_page, rev_timestamp as created_when, rev_len original_size
  from revision
  join page
  on rev_page = page_id
  where rev_parent_id = 0 AND page_namespace = 0 AND page_is_redirect = 0
  #AND rev_timestamp BETWEEN 20160101000000 AND 20161230000000
  AND page_random > RAND()
  AND page_title IN
  (SELECT page_title
FROM page
INNER JOIN categorylinks
WHERE cl_from = page_id
AND cl_to = 'بوابة_السعودية/مقالات_متعلقة'
AND page_namespace = 0)
) ori
join revision r1
on ori.rev_page = r1.rev_page
left join revision r2
on r1.rev_page = r2.rev_page
and r2.rev_timestamp > r1.rev_timestamp
where r2.rev_timestamp is null
SQL

Checking query status...
