Fork of Did translated articles grow? by Huji
This query is marked as a draft This query has been published by UOzurumba (WMF).

SQL

​x
 
use fawiki_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, page_namespace, 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 rev_comment like '%ابزار/به_ویکی•فا%'
  and page_random > RAND()
  limit 1000
) 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
​
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...