Average age of hrwiki articles

Fork of Average age of WP Croatia articles by GregorB

This query is marked as a draft This query has been published by GregorB.

SQL

use hrwiki_p;
# 20050101   4874 132.4
# 20060101  11961  56.0
# 20070101  25894  63.9
# 20080101  40636 101.1
# 20090101  56647 112.7
# 20100101  79730 149.6
# 20110101  97987 207.6
# 20120101  
select count(*), round(avg(age), 1) as avg_age
from (
  select pa.page_id, 
  str_to_date(max(r.rev_timestamp), '%Y%m%d') as ts, 
  datediff(date '20120101', str_to_date(max(r.rev_timestamp), '%Y%m%d')) - 1 as age
  from page pa 
  inner join revision r on r.rev_page = pa.page_id
  where pa.page_namespace in (0, 102) and pa.page_is_redirect = 0 and
  r.rev_timestamp < '20120101'
  group by pa.page_id
) t

SQL

Checking query status...